Performance Problem bei mysql Abfrage über 3 Tables

[Alexander]

Hallo,

ich denke dass das eigentlich eine recht einfache Frage ist. Nur leider bin ich momentan wie vor den Kopf gestossen. Ich habe folgenden Abfragestring:

SELECT a.GC_NR, a.GC_ID, a.GC_NAME, a.GC_TEL, a.GC_MAIL, a.GC_WWW, a.GC_STR, a.GC_PLZ, a.GC_ORT, b.FORM_FINISH, b.FORM_USER, c.GC_SORT
FROM gg_gc AS a, gg_allg AS b, gg_admin_pdf AS c
WHERE a.GC_ID = b.GC_ID AND a.GC_ID = c.GC_ID AND b.FORM_FINISH = ""
ORDER BY a.GC_NR

In den Tabellen stehen jeweils ca 700 bis 800 Datensätze. Wenn diese Abfrage gestartet wird, geht die Serverauslastung auf 99,0 Prozent hoch. (Bei einem Einzelnen Aufruf) Ich denke ich stelle mich jetz einfach zu blöd an. Wie kann ich denn die Abfrage Optimieren? (drehe mich im Kreis)

Viele Grüße
Alexander

[mwanaheri]

Wie wärs, wenn du einen join verwenden würdest oder einen view? könnte das nicht schneller sein?

[Hans-Georg Normann]

@Alexander
Hmm, wie wäre es wenn du erst einmal den Code aufräumst, dann siehste vielleicht auch was passiert. Ich mach das mal aber lass das nicht zur Gewohnhweit werden

SELECT 
   a.GC_NR, 
   a.GC_ID, 
   a.GC_NAME, 
   a.GC_TEL, 
   a.GC_MAIL, 
   a.GC_WWW, 
   a.GC_STR, 
   a.GC_PLZ, 
   a.GC_ORT, 
   b.FORM_FINISH, 
   b.FORM_USER, 
   c.GC_SORT
FROM gg_gc AS a, gg_allg AS b, gg_admin_pdf AS c
WHERE 
       a.GC_ID = b.GC_ID 
   AND a.GC_ID = c.GC_ID 
   AND b.FORM_FINISH = ""
ORDER BY a.GC_NR

Erzähl mal ein Bisschen über den Aufbau der Tabellen. Richtig rennen wird die Abfrage, wenn in b.GC_ID ind c.GC_ID Indexe existieren, wenn möglich UNIQUE.

Was hat eigentlich zu passieren, wenn b.GC_ID oder c.GC_ID nicht gefunden werden können?

@mwanaheri
Is doch ein Inner Join oder willst du mir was anderes erzählen?

Hans

[mwanaheri]

@mwanaheri
Is doch ein Inner Join oder willst du mir was anderes erzählen?
Hans

Klar, im Effekt auf jeden Fall, aber kann man das bei MySQL nicht auch anders formulieren? Mein Gedanke dabei ist, dass dann das DBMS _möglicherweise_ intern etwas optimiert vorgehen kann. Ist aber nur geraten.
ebenfalls:

[Hans-Georg Normann]

Ist eigentlich egal, ob du die hier gewählte Form oder die Klausel .... INNER JOIN .... anwendest, zumindest weiss ich es nicht anders.

Entscheidend ist, das in dem Beispiel die Tabellen gg_allg und gg_admin_pdf jeweils ein INDEX für das Feld GC_ID exisziert. Dann wird indiziert zugegriffen.

Hans

[mwanaheri]

Na, das könnte noch vom optimierer des dbms abhängen, aber da lasse ich mich gern eines Besseren belehren. Aber der Hinweis auf den Index ist natürlich eine wichtige Frage an den Originalposter, denn das würde sicherlich helfen.

[Jasper]

führe das statement mit explain aus:

explain select ....

und poste mal den output.


-j

[Jasper]

Entscheidend ist, das in dem Beispiel die Tabellen gg_allg und gg_admin_pdf jeweils ein INDEX für das Feld GC_ID exisziert. Dann wird indiziert zugegriffen.

indizierter zugriff ist langsamer als nicht indizierter wenn die ergebnismenge ca. 10-15% (abhängig von den daten) der gesamtmenge ist. da bisher nicht bekannt ist, wieviele datentupel überhaupt in der ergebnismenge enthalten sind, würde ich keine indizes auf verdacht anlegen. zur verdeutlichung;

mysql> explain select * from a,b,c where a.id = b.id and a.id = c.id and b.finish='' order by a.nr;
+-------+-------+---------------+------+---------+------+------+---------------------------------+
| table | type  | possible_keys | key  | key_len | ref  | rows | Extra                           |
+-------+-------+---------------+------+---------+------+------+---------------------------------+
| a     | ALL   | a1            | NULL |    NULL | NULL |    3 | Using temporary; Using filesort |
| b     | ALL   | b1            | NULL |    NULL | NULL |    3 | where used                      |
| c     | index | c1            | c1   |       5 | NULL |    3 | where used; Using index         |
+-------+-------+---------------+------+---------+------+------+---------------------------------+
3 rows in set (0.00 sec)

mysql> select * from a,b,c where a.id = b.id and a.id = c.id and b.finish='' order by a.nr;
+------+------+------+--------+------+
| id   | nr   | id   | finish | id   |
+------+------+------+--------+------+
|    2 |    2 |    2 |        |    2 |
+------+------+------+--------+------+
1 row in set (0.00 sec)

a1,b1,c1 sind übrigens unique indizes


-j

[Hans-Georg Normann]

Hi Jasper

das will mir nicht einleuchten Ist das Datenbankverhalten unter Firebird anders als unter MySQL? Arbeite normal nur mit Firebird und da sind fehlende Indexe ein Performancekiller.

Hans

[Jasper]

das will mir nicht einleuchten Ist das Datenbankverhalten unter Firebird anders als unter MySQL? Arbeite normal nur mit Firebird und da sind fehlende Indexe ein Performancekiller.

ein indexzugriff sind immer 2 logische zugriffe (spezialitäten wie IOT unter oracle mal aussen vor gelassen):

1x index um die row-id zu erhalten, 1x heap (oder wo auch immer die daten liegen) für die daten. das sind aber logische zugriffe, je nach tiefe des indexes kann 1 logischer zugriff auf den index 2-3 (tiefere indizes sind zu vermeiden) physikalische zugriffe sein.

angenommen, eine tabelle hat 100 blöcke (DBs arbeiten intern mit blöcken). eine spalte ist indiziert und der index belegt 30 blöcke. nun ein paar worst-case-szenarien:

erster fall: selektiere 1 zeile
das kostet 3 blöcke (indextiefe 3) für den index und 1 block für die tabelle = 4 blöcke
ein FTS kostet dagegen 100 blöcke. klarer fall für index

zweiter fall: selektiere 10 zeilen
das kostet pro zeile genausoviel wie für 1, also 4 blöcke * 10 = 40
ein FTS kostet dagegen immer noch 100. hier wird es schon eng, weil index-zugriffe meistens über scattered read (verteiltes lesen) erfolgen, FTS dagegen über sequential read (sequentielles lesen, dass durch wegfall des kopfpositionierens schneller als scattered ist).

dritter fall: selektiere 100 zeilen
4 blöcke * 100 = 400 blöcke. FTS dagegen 100. ouch.

caching, optimierungen, etc. pp. aussen vor gelassen. ich hab das alles sehr vereinfacht dargestellt, in der realität sieht das alles etwas anders aus. der einfachheit bin ich z.b. von von einer zeile / block ausgegangen, was in der praxis sehr selten vorkommt.

man sieht aber, dass ab einem gewissen quotienten returned_set/total_set die performance kippt. deshalb sind regelbasierte gegenüber kostenbasierten optimierer gnadenlos im nachteil wenn es um mittlere/grosse datenbanken geht. kostenbasierte optimierer machen nämlich genau diese berechnungen bevor irgendein index zum einsatz kommt um den optimalen zugriffspfad zu ermitteln. ob firebird einen RBO oder CBO verwendet weiss ich nicht.

index-zugriff profitiert von einer hohen diversität des index. bestes negativbeispiel ist die spalte geschlecht: hat exakt 2 werte und diese werte sind i.d.R. gleichverteilt. da macht ein index keine sinn und ist kontraproduktiv. teste mal firebird mit diesem beispiel. wenn firebird immer einen index verwendet, hat firebird einen RBO.


-j

[Christoph]

index-zugriff profitiert von einer hohen diversität des index. bestes negativbeispiel ist die spalte geschlecht: hat exakt 2 werte und diese werte sind i.d.R. gleichverteilt. da macht ein index keine sinn und ist kontraproduktiv. teste mal firebird mit diesem beispiel. wenn firebird immer einen index verwendet, hat firebird einen RBO.

In diesem Fall sollte man einen Bitmap-Index verwenden (in Oracle: CREATE BITMAP INDEX ...), der speziell für Felder mit wenigen etwa gleichverteilten Werten gedacht ist.

Zum Rest kann ich lediglich ergänzen, dass Datenbanken mit einem sehr ausgefeilten Query-Optimizer wie z.B. PostgreSQL selbstständig entscheiden, ob die Indexbenutzung die Abfrage beschleunigt. Dazu sammelt PostgreSQL statistische Infos über Tabellen, die explizit mit ANALYZE aktualisiert werden müssen (keine Ahnung warum Postgres sich da nicht selber drum kümmern kann).

Bei den Tests mit MySQL (Version 4.0.20), die ich mal gemacht habe, haben Indizes merkwürdigerweise durchgängig zu eienr Verlangsamung aller Abfragen geführt. Allerdings bin ich dem Phänomen nicht weiter nachgegangen, da ich MySQL nicht einsetze.

[Jasper]

In diesem Fall sollte man einen Bitmap-Index verwenden (in Oracle: CREATE BITMAP INDEX ...), der speziell für Felder mit wenigen etwa gleichverteilten Werten gedacht ist.

aber nur, wenn auf die so indizierte spalte wenig dml-statements abgesetzt werden.
das aktualisieren von bitmap-indizes frisst resourcen (bedeutend mehr als btree).


-j