Grundsaetzliches zu JOIN und INDEX

Hallo Gott! Hallo Welt!

Ich versuche gerade JOINs zu optimieren.

Nehmen wir an, ich hätte ein SELECT wie zum Beispiel das Folgende:

SELECT
`vw artist`.`artist name`,
`vw style`.`style name`
FROM
`vw artist`
LEFT JOIN
`vw artist has style`
ON
`vw artist`.`id` = `vw artist has style`.`artist id`
LEFT JOIN
`vw style`
ON
`vw artist has style`.`style id` = `vw style`.`id`
WHERE
`vw style`.`id` = 10

Das SELECT könnte jetzt noch um zahlreiche weitere JOINs, wie zum
Beispiel "artist has type" und "artist has songs" erweitert werden, aber
immer nach genau dem selben Schema wie oben. (Im Prinzip stellt das ganze
eine Künstlersuche nach bestimmten Kriterien dar.)

Ich habe nun folgende Indexe:

`vw artist`
PRIMARY KEY `id`
`vw artist has style`
PRIMARY KEY (`artist id`,`style id`)
`vw style`
PRIMARY KEY `id`

Genügt das?` Inbesondere bei `vw artist has style` bin ich mir unsicher,
weil es ja ein "Doppelkey" ist, aber geJOINt wird ja mit den einzelnen
Spalten. Brauchen beide Spalten noch einen eigenen Key?

Macht es einen Unterschied in welcher Reihenfolge ich JOINe?

Ich habe mich schon an EXPLAIN versucht, werde aber aus der Ausgabe nicht
viel schlauer.

Kennt vielleicht jemand ein gutes Buch oder Tutorial, das verständlich
erklärt, wie man schnelle JOINs erstellt?

Ich bin zwar großer Freund der MySQL Online Dokumentation, aber in diesem
Punkt hat sie mir nicht so richtig weiter geholfen.

Vielen Dank schon mal.

Ciao

Alex

Re: Grundsaetzliches zu JOIN und INDEX

Alexander Vipach wrote:
> Hallo Gott!

Du darfst mich "Axel" nennen ;-)

> Ich versuche gerade JOINs zu optimieren.
> Nehmen wir an, ich hätte ein SELECT wie zum Beispiel das Folgende:
>
> SELECT
> `vw artist`.`artist name`,
> `vw style`.`style name`
> FROM
> `vw artist`
> LEFT JOIN
> `vw artist has style`
> ON
> `vw artist`.`id` = `vw artist has style`.`artist id`
> LEFT JOIN
> `vw style`
> ON
> `vw artist has style`.`style id` = `vw style`.`id`
> WHERE
> `vw style`.`id` = 10

Vorab: du hast Leerzeichen in Tabellen- bzw. Spaltennamen?

Schlechte Idee. Ohne Leerzeichen könntest du die Quotezeichen
weglassen (vor allem: nie vergessen) und es würde besser lesbar.

> Das SELECT könnte jetzt noch um zahlreiche weitere JOINs, wie zum
> Beispiel "artist has type" und "artist has songs" erweitert werden, aber
> immer nach genau dem selben Schema wie oben. (Im Prinzip stellt das ganze
> eine Künstlersuche nach bestimmten Kriterien dar.)

Erste Frage: müssen das tatsächlich OUTER JOINs sein? D.h.
a) hast du Künstler für die keine styles existieren und
b) willst du die mit style NULL angezeigt bekommen?

> Ich habe nun folgende Indexe:
>
> `vw artist`
> PRIMARY KEY `id`
> `vw artist has style`
> PRIMARY KEY (`artist id`,`style id`)
> `vw style`
> PRIMARY KEY `id`
>
> Genügt das?

Für oben stehende Query: Ja. Bei OUTER JOINs hat der Optimizer ja
ohnehin keine Wahlmöglichkeiten bei der Reihenfolge der Tabellen
(insbesondere wenn die Attribute so verkettet sind wie oben).

MySQL muß in jedem Fall `vw artist` komplett durchlaufen, zu jeder
Zeile die passende(n) Zeile(n) aus `vw artist has style` finden
(dazu gibts einen Index, paßt also) und dann zu diesen Tupeln die
passende Zeile aus `vw style`. Anschließend wird zu jeder Zeile des
Zwischergebnisses die passende Zeile aus `vw style` gesucht und erst
jetzt alle Zeilen verworfen, die nicht dem WHERE entsprechen. Nicht
effizient, weil ein viel zu großes Zwischenergebnis entsteht.


Wenn das hingegen INNER JOINs wären, dann würde der Optimizer die
Sache vermutlich "von hinten" aufrollen. Das WHERE findet genau eine
Zeile in `vw styles` (id ist ja UNIQUE). Um aber die passenden Zeilen
in `vw artist has style` effizient finden zu können, müßte diese
Tabelle einen Index auf `style id` haben. Nemen wir an, den hätte
sie, dann wird zuletzt `vw artist` über den PK gejoint. Alles gut.

Wenn du die Ausgabe von EXPLAIN dafür anschaust, kannst du sehen,
was MySQL wirklich macht (ich schätze: Scan auf `vw artist has style`
JOIN mit `vw styles` unter Berücksichtigung des WHERE, schließlich
JOIN mit `vw artist` über den PK)

> Inbesondere bei `vw artist has style` bin ich mir unsicher,
> weil es ja ein "Doppelkey" ist, aber geJOINt wird ja mit den einzelnen
> Spalten.

Solange nach `artist id` gesucht wird (wie in obigem LEFT JOIN), paßt
es. Wenn der JOIN mit `vw styles` auch in die Gegenrichtung effizient
sein soll, fehlt noch ein Index auf `style id`.

> Macht es einen Unterschied in welcher Reihenfolge ich JOINe?

Nein. Außer daß bei LEFT/RIGHT JOIN die Reihenfolge wesentlich ist.
Aber ob du A LEFT JOIN B oder B RIGHT JOIN A schreibst, ist egal.

> Ich habe mich schon an EXPLAIN versucht, werde aber aus der Ausgabe nicht
> viel schlauer.

Das Handbuch erklärt das eigentlich ganz gut
Welcher Teil ist dir denn nicht klar?

> Kennt vielleicht jemand ein gutes Buch oder Tutorial, das verständlich
> erklärt, wie man schnelle JOINs erstellt?

Eigentlich beginnt man damit, daß man die offensichtlichen Indexe
anlegt und erst wenn die JOINs zu langsam werden, mit EXPLAIN nach-
schaut warum und dann die notwendigen zusätzlichen Indexe hinzufügt.

Bei INNER JOINs ist es meistens hilfreich, Indexe auf den jeweils
korrespondierenden Spalten zu haben. Dann hat der Optimizer mehr
Möglichkeiten.


XL

Re: Grundsaetzliches zu JOIN und INDEX

Axel Schwenke schrieb:

>> SELECT
>> `vw artist`.`artist name`,
>> `vw style`.`style name`
>> FROM
>> `vw artist`
>> LEFT JOIN
>> `vw artist has style`
>> ON
>> `vw artist`.`id` = `vw artist has style`.`artist id`
>> LEFT JOIN
>> `vw style`
>> ON
>> `vw artist has style`.`style id` = `vw style`.`id`
>> WHERE
>> `vw style`.`id` = 10
....
> Erste Frage: müssen das tatsächlich OUTER JOINs sein? D.h.
> a) hast du Künstler für die keine styles existieren und
> b) willst du die mit style NULL angezeigt bekommen?
....
> Wenn das hingegen INNER JOINs wären, dann würde der Optimizer die
> Sache vermutlich "von hinten" aufrollen. Das WHERE findet genau eine
> Zeile in `vw styles` (id ist ja UNIQUE). Um aber die passenden Zeilen
> in `vw artist has style` effizient finden zu können, müßte diese
> Tabelle einen Index auf `style id` haben. Nemen wir an, den hätte
> sie, dann wird zuletzt `vw artist` über den PK gejoint. Alles gut.

Wenn es tatsächlich INNER JOINS wären, könnte man das Statement auch
verkürzen, oder? Der JOIN auf `vw style` wäre gar nicht notwendig:

SELECT
`vw artist`.`artist name`,
`vw style`.`style name`
FROM
`vw artist`
JOIN `vw artist has style`
ON `vw artist`.`id` = `vw artist has style`.`artist id`
WHERE `vw artist has style`.`style_id` = 10

Wobei ich mir jetzt nicht sicher bin, ob es in diesem Fall nicht beim
OUTER JOIN auch so ist.

gruss, heli

Re: Grundsaetzliches zu JOIN und INDEX

Helmut Chang wrote:

>> Erste Frage: müssen das tatsächlich OUTER JOINs sein? D.h.
>> a) hast du Künstler für die keine styles existieren und
>> b) willst du die mit style NULL angezeigt bekommen?

Dann aber nicht mit dieser Abfrage :-)

> Wenn es tatsächlich INNER JOINS wären, könnte man das Statement auch
> verkürzen, oder? Der JOIN auf `vw style` wäre gar nicht notwendig:

ack

> SELECT
> `vw artist`.`artist name`,
> `vw style`.`style name`
> FROM
> `vw artist`
> JOIN `vw artist has style`
> ON `vw artist`.`id` = `vw artist has style`.`artist id`
> WHERE `vw artist has style`.`style_id` = 10
>
> Wobei ich mir jetzt nicht sicher bin, ob es in diesem Fall nicht beim
> OUTER JOIN auch so ist.

Da stand zwar Outer Join, aber das Ergebnis ist das eines Inner Joins,
da auf einer Spalte gesucht wird, die mit NULLs aufgefüllt wird.
Und jeder Vergleich mit NULL ist immer UNKNOWN :-)
Ein guter Optimizer würde das erkennen und den blödsinnigen Outer Join
einfach in einen Inner umwandeln.

Alles mit Outer Joins zu schreiben ist übrigens weit verbreitet bei
mysql Usern, scheinbar lesen doch manche das Manual und da steht ja ein
Kapitel über "How MySQL Optimizes LEFT JOIN" und dann muss das wohl
besser optimiert sein als ein Inner Join ;-)

Dieter

Re: Grundsaetzliches zu JOIN und INDEX

Helmut Chang wrote:
> Axel Schwenke schrieb:

[snip]

> Wenn es tatsächlich INNER JOINS wären, könnte man das Statement auch
> verkürzen, oder? Der JOIN auf `vw style` wäre gar nicht notwendig:
>
> SELECT
> `vw artist`.`artist name`,
> `vw style`.`style name`
> FROM
> `vw artist`
> JOIN `vw artist has style`
> ON `vw artist`.`id` = `vw artist has style`.`artist id`
> WHERE `vw artist has style`.`style_id` = 10

Huh? So wie ich das sehe, hat Alexander das sauber durchnormalisiert.
`vw artist has style` ist eine reine Verknüpfungstabelle für die N:M
Relation zwischen Artisten und Styles. Um an das `Name` Attribut des
Styles zu kommen, muß man also mit der Style Tabelle joinen.


XL

Re: Grundsaetzliches zu JOIN und INDEX

Dieter Noeth schrieb:

>> SELECT
>> `vw artist`.`artist name`,
>> `vw style`.`style name`
>> FROM
>> `vw artist`
>> JOIN `vw artist has style`
>> ON `vw artist`.`id` = `vw artist has style`.`artist id`
>> WHERE `vw artist has style`.`style_id` = 10
>>
>> Wobei ich mir jetzt nicht sicher bin, ob es in diesem Fall nicht beim
>> OUTER JOIN auch so ist.
>
> Da stand zwar Outer Join, aber das Ergebnis ist das eines Inner Joins,
> da auf einer Spalte gesucht wird, die mit NULLs aufgefüllt wird.

Da bin ich beruhigt, dass meine SQL-Kenntnisse doch noch nicht so
eingerostet sind ;-).

gruss, heli

Re: Grundsaetzliches zu JOIN und INDEX

Axel Schwenke schrieb:

>> SELECT
>> `vw artist`.`artist name`,
>> `vw style`.`style name`
>> FROM
>> `vw artist`
>> JOIN `vw artist has style`
>> ON `vw artist`.`id` = `vw artist has style`.`artist id`
>> WHERE `vw artist has style`.`style_id` = 10
>
> ...Um an das `Name` Attribut des
> Styles zu kommen, muß man also mit der Style Tabelle joinen.

Sorry, natürlich hast du recht. Ich hab mir gar nicht angesehen, was
eigentlich selektiert wird.

gruss, heli

Re: Grundsaetzliches zu JOIN und INDEX

Hallo Axel! Hallo Gott! Hallo Welt!

> Du darfst mich "Axel" nennen ;-)

Klar, mach ich doch gern, aber hier lesen ja noch mehr Leute mit! :-P

> Vorab: du hast Leerzeichen in Tabellen- bzw. Spaltennamen?

Jupp!

> Schlechte Idee. Ohne Leerzeichen könntest du die Quotezeichen
> weglassen (vor allem: nie vergessen) und es würde besser lesbar.

Mmmhhh, ich finds mit Leerzeichen besser lesbar. Ist also wohl
Geschmackssache. Technisch hat das keine Nachteile, oder?

> Erste Frage: müssen das tatsächlich OUTER JOINs sein? D.h.
> a) hast du Künstler für die keine styles existieren und

Jupp, zur Zeit können einem Künstler 0-2 Styles zugewiesen werden. Aber
in obigem Beispiel interessieren mich natürlich nur die, die wenigstens
einen Style haben.

> b) willst du die mit style NULL angezeigt bekommen?

Nee, die brauch ich in der Tat nicht.

OK, ich habe mir jetzt nochmal INNER und OUTER JOINs durchgelesen. Wenn
ich das richtig verstehe ist es zum Suchen aller Artists eines Styles
besser einen INNER JOIN zu verwenden:

SELECT
`vw artist`.`artist name`,
`vw style`.`style name`
FROM
`vw artist`
INNER JOIN
`vw artist has style`
ON
`vw artist`.`id` = `vw artist has style`.`artist id`
INNER JOIN
`vw style`
ON
`vw artist has style`.`style id` = `vw style`.`id`
WHERE
`vw style`.`id` = 10

Aber zum Ausgeben aller Artists mit ihren entsprechenden Styles einen
LEFT JOIN:

SELECT
`vw artist`.`artist name`,
`vw style`.`style name`
FROM
`vw artist`
LEFT JOIN
`vw artist has style`
ON
`vw artist`.`id` = `vw artist has style`.`artist id`
LEFT JOIN
`vw style`
ON
`vw artist has style`.`style id` = `vw style`.`id`

Ist das richtig?

Wie kann man das ganze am besten messen, um verschiedene SQL-Statements
miteinander zu vergleichen? BENCHMARK scheint nur zum Testen von
Funktionen da zu sein. MySQL cacht nämlich scheinbar die Ergebnisse,
denn, wenn ich einen der obigen Ausdrücke das erste Mal ausführe dauert
er ungefähr eine drittel Sekunde und danach nur noch ein Hundertstel
davon.

Was mich übrigens auch zu der Erkenntnis bringt, das MySQL intern beide
oben beschriebenen Statements zum selben Statement optimiert. Denn habe
ich eines von beiden einmal ausgeführt, gehen beide viele schneller.

>> Ich habe mich schon an EXPLAIN versucht, werde aber aus der Ausgabe
>> nicht viel schlauer.
>
> Das Handbuch erklärt das eigentlich ganz gut
> Welcher Teil ist dir denn nicht klar?

Mmmhhh, ist ein bißchen schwer die Ausgabe hier her zu kopieren:

+----+-------------+---------------------+--------+--------- ------
+---------+---
------+---------------------------------------------+------
+--------------------
------+
| id | select_type | table | type | possible_keys | key
| ke
y_len | ref | rows | Extra
|
+----+-------------+---------------------+--------+--------- ------
+---------+---
------+---------------------------------------------+------
+--------------------
------+
| 1 | SIMPLE | vw style | const | PRIMARY |
PRIMARY | 4
| const | 1 |
|
| 1 | SIMPLE | vw artist has style | index | PRIMARY |
PRIMARY | 8
| NULL | 2932 | Using where;
Using
index |
| 1 | SIMPLE | vw artist | eq_ref | PRIMARY |
PRIMARY | 4
| vw.vw artist has style.artist id | 1 |
|
+----+-------------+---------------------+--------+--------- ------
+---------+---
------+---------------------------------------------+------
+--------------------
------+

Beide JOINs, egal ob LEFT oder INNER JOIN bieten diese identische
"Erklärung" und ehrlich gesagt, weiß ich nicht wie sie zu lesen ist. Ist
alles OK? Muß ich mir Sorgen machen, weil nur in der mittleren Zeile in
der Spalte Extra Indexe angegeben sind, die genutzt werden?

Vielen Dank jedenfalls schon mal für die Mühe die Du Dir gemacht hast.

Ciao

Alex

Re: Grundsaetzliches zu JOIN und INDEX

Am 02.10.2006 15:46 schrieb Alexander Vipach:
> Hallo Axel! Hallo Gott! Hallo Welt!

>> Schlechte Idee. Ohne Leerzeichen könntest du die Quotezeichen
>> weglassen (vor allem: nie vergessen) und es würde besser lesbar.
>
> Mmmhhh, ich finds mit Leerzeichen besser lesbar. Ist also wohl
> Geschmackssache. Technisch hat das keine Nachteile, oder?
>

Nein, hat es nicht. Es ist eben "unüblich", weil man jeden
Tabellennamen und teilweise sogar die Spalten mit ` quoten muss:

> SELECT
> `vw artist`.`artist name`,
> `vw style`.`style name`
> FROM
> `vw artist`
> INNER JOIN
> `vw artist has style`
> ON
> `vw artist`.`id` = `vw artist has style`.`artist id`
> INNER JOIN
> `vw style`
> ON
> `vw artist has style`.`style id` = `vw style`.`id`
> WHERE
> `vw style`.`id` = 10
>
Die Alternative ohne Leerzeichen:

SELECT
vw_artist.artist_name,
vw_style.style_name
FROM
vw_artist
INNER JOIN
vw_artist_has_style
ON
vw_artist.id = vw_artist_has_style.artist_id
INNER JOIN
vw_style
ON
vw_artist_has_style.style_id = vw_style.id
WHERE
vw_style.id = 10

Das rauscht, da man sich die unzähligen ` spart, deutlich weniger,
lässt sich also schneller verstehen.

Ohnehin könnte man darüber diskutieren, ob man wirklich mit dem
immerselben Präfix arbeiten oder sowas nicht raus-faktorisieren kann.

Re: Grundsaetzliches zu JOIN und INDEX

Alexander Vipach wrote:
>
> OK, ich habe mir jetzt nochmal INNER und OUTER JOINs durchgelesen. Wenn
> ich das richtig verstehe ist es zum Suchen aller Artists eines Styles
> besser einen INNER JOIN zu verwenden:
[SNIP]
> Aber zum Ausgeben aller Artists mit ihren entsprechenden Styles einen
[SNIP]
>
> Ist das richtig?

Genau so ist es. Grundsätzlich solltest du immer dann INNER JOIN
verwenden, wenn du die Zeilen, für die es keine passenden Zeilen in
einer anderen Tabelle gibt, nicht brauchst.
Brauchst du die Info, egal ob es passende Zeilen gibt, dann OUTER JOIN.

Sonderfall: Genau die Zeilen, zu denen es in der anderen Tabelle nichts
passendes gibt:
SELECT a.* FROM a LEFT JOIN b ON b.aid = a.id WHERE b.id IS NULL

> Wie kann man das ganze am besten messen, um verschiedene SQL-Statements
> miteinander zu vergleichen? BENCHMARK scheint nur zum Testen von
> Funktionen da zu sein. MySQL cacht nämlich scheinbar die Ergebnisse,
> denn, wenn ich einen der obigen Ausdrücke das erste Mal ausführe dauert
> er ungefähr eine drittel Sekunde und danach nur noch ein Hundertstel
> davon.

AFAIK hilft zum Leeren des Querycaches nur ein Neustarten des Servers.
Aber es kann durchaus sein, daß es eine Option gibt, die den Cache
einfach abschaltet. Dann hättest du bei jeder Abfrage ein jungfräuliches
MySQL vor dir.

> Was mich übrigens auch zu der Erkenntnis bringt, das MySQL intern beide
> oben beschriebenen Statements zum selben Statement optimiert. Denn habe
> ich eines von beiden einmal ausgeführt, gehen beide viele schneller.

Was zeigt, daß der Optimizer gut gearbeitet hat. Aber man kann es ihm ja
trotzdem leichter machen, dann kann er sich den Aufwand sparen. Das
führt dann halt zu mehr Performance, aber darüber kann man ja hinwegsehen ;)

> Mmmhhh, ist ein bißchen schwer die Ausgabe hier her zu kopieren:

Schließ mal die Zeile mit \G ab, statt mit einem Semikolon. Dann wird's
hübscher dargestellt.


Grüße,
Dominik
--
http://www.vlights.com/
vLights.com - Das Portal für virtuelle Kerzen

Re: Grundsaetzliches zu JOIN und INDEX

Alexander Vipach wrote:

> OK, ich habe mir jetzt nochmal INNER und OUTER JOINs durchgelesen. Wenn
> ich das richtig verstehe ist es zum Suchen aller Artists eines Styles
> besser einen INNER JOIN zu verwenden ...
> Aber zum Ausgeben aller Artists mit ihren entsprechenden Styles einen
> LEFT JOIN

Wie gesagt: nur wenn du auch Künstler ohne Style angezeigt bekommen
möchtest. Dann *mußt* du einen OUTER JOIN nehmen. Ob das im Kontext
deiner Anwendung sinnvoll ist, weißt nur du.

> Wie kann man das ganze am besten messen, um verschiedene SQL-Statements
> miteinander zu vergleichen?

Das MySQL-Frontend mißt (unter Linux) auf 1/100s genau. Das sollte für
die Praxis ausreichen.

> MySQL cacht scheinbar die Ergebnisse,
> denn wenn ich einen der obigen Ausdrücke das erste Mal ausführe dauert
> er ungefähr eine drittel Sekunde und danach nur noch ein Hundertstel
> davon.

Das ist der ->Query-Cache. Zum Benchmarken solltest du den abschalten.
Kannst du aber auch per "magischem" Kommentar im SQL-Statement machen.
Lies halt mal den Abschnitt des Handbuchs zum Query-Cache.

> Was mich übrigens auch zu der Erkenntnis bringt, das MySQL intern beide
> oben beschriebenen Statements zum selben Statement optimiert. Denn habe
> ich eines von beiden einmal ausgeführt, gehen beide viele schneller.

Nein. Der Query-Cache erkennt nur exakt gleiche Query-Strings wieder.
Aber natürlich gibt es noch andere Caches, z.B. für Indexe.

>>> Ich habe mich schon an EXPLAIN versucht, werde aber aus der Ausgabe
>>> nicht viel schlauer.
>>
>> Das Handbuch erklärt das eigentlich ganz gut
>> Welcher Teil ist dir denn nicht klar?
>
> Mmmhhh, ist ein bißchen schwer die Ausgabe hier her zu kopieren:

Der Hinweis mit \G kam hier erst vor kurzem. Wenn du im Kommandozeilen-
client das Kommando statt mit ';' mit '\G' abschließt, werden Tabellen
vertikal dargestellt und sind dann hier leichter zu lesen.

Ich verkürze mal auf das wichtigste:

+---------------------+---------+------+-------------------- ------+
| table | key | rows | Extra |
+---------------------+---------+------+-------------------- ------+
| vw style | PRIMARY | 1 | |
| vw artist has style | PRIMARY | 2932 | Using where; Using index |
| vw artist | PRIMARY | 1 | |
+---------------------+---------+------+-------------------- ------+

> Beide JOINs, egal ob LEFT oder INNER JOIN bieten diese identische
> "Erklärung"

Das ist gut! Das bedeutet nämlich, daß der Optimizer die Sinnlosigkeit
des OUTER JOINs erkennt und zum äquivalenten INNER JOIN übergeht.

> ehrlich gesagt, weiß ich nicht wie sie zu lesen ist. Ist alles OK?

Gehen wir es mal durch:

Die Reihenfolge der Zeilen gibt an, in welcher Reihenfolge MySQL die
Tabellen verwendet. Im Prinzip wird ein JOIN wie folgt abgearbeitet:

1 selektiere die notwendigen Felder aus der ersten Tabelle in das
vorläufige Ergebnis
2 für jede weitere Tabelle:
3 für jede Zeile des vorläufigen Ergebnisses:
4 kombiniere die Ergebniszeile mit den passenden Tabellenzeilen
5 wenn möglich: wirf unpassende Ergebniszeilen weg
6 final: wirf unpassende Ergebnis-Zeilen weg
7 sortiere das Ergebnis

Für gute Performance sind Schritte 4 und 5 wichtig: Schritt 4 sollte
einen Index nutzen, um passende Zeilen zu finden. Schritt 5 verringert
die Zeilenzahl im (vorläufigen) Ergebnis und verringert dadurch die
Anzahl der Schleifendurchläufe für die nächste(n) Tabelle(n).

Die wichtigsten Spalten im EXPLAIN Output sind 'key' und 'rows'.
Dabei nennt 'key' den Index, der in diesem Schritt verwendet wird.
Wenn da NULL steht, heißt das, es gibt keinen passenden Index.
Die Spalte 'rows' enthält die geschätzte Anzahl Treffer in dieser
Tabelle. Wenn du alle Werte in der 'rows' Spalte multiplizierst,
bekommst du einen Anhaltspunkt, wieviele Zeilen das Zwischenergeb-
niss in der Datenbank hat.

Je größer dieses Produkt, desto teurer der JOIN.

Die 'Extra' Spalte liefert weitere Informationen: 'Using where'
bedeutet, daß beim JOINen dieser Tabelle Zeilen ausgesiebt werden
(Schritt 5 oben!). 'Using index' ist auch gut, es bedeutet, daß
alle notwendigen Felder aus dem Index genommen werden können -
es ist also gar kein Zugriff auf die Daten notwendig.


HTH, XL

Re: Grundsaetzliches zu JOIN und INDEX

Hallo Axel! Hallo Gott! Hallo Welt!

> Das MySQL-Frontend mißt (unter Linux) auf 1/100s genau. Das sollte für
> die Praxis ausreichen.

Ja, liefert aber jedesmal leicht unterschiedliche Ergebnisse und mich
interessiert ja in der Regel nur das erste ungecachte Ergebnis. Ich weiß
nicht wie groß der Query-Cache von MySQL bei den meisten Webservern ist,
denke aber mal, das in der Regel nur ungecachte Queries ausgeführt
werden.

> Das ist der ->Query-Cache. Zum Benchmarken solltest du den abschalten.
> Kannst du aber auch per "magischem" Kommentar im SQL-Statement machen.
> Lies halt mal den Abschnitt des Handbuchs zum Query-Cache.

Mach ich! Merci!

> Die 'Extra' Spalte liefert weitere Informationen: 'Using where'
> bedeutet, daß beim JOINen dieser Tabelle Zeilen ausgesiebt werden
> (Schritt 5 oben!). 'Using index' ist auch gut, es bedeutet, daß
> alle notwendigen Felder aus dem Index genommen werden können -
> es ist also gar kein Zugriff auf die Daten notwendig.

Vielen Dank für die sehr ausführliche Erklärung! Jetzt weiß ich deutlich
mehr und wenn ich es richtig verstehe gibt es an meinem JOIN nachdem ich
es auf INNER JOIN umgestellt habe nichts mehr zu meckern! :-)

Ciao

Alex

Re: Grundsaetzliches zu JOIN und INDEX

Hallo Christian! Hallo Gott! Hallo Welt!

> Nein, hat es nicht. Es ist eben "unüblich", weil man jeden
> Tabellennamen und teilweise sogar die Spalten mit ` quoten muss:
> [...]
> Die Alternative ohne Leerzeichen:

Du hast mich überzeugt. Finde das mit Unterstrichen jetzt auch besser. Werd
ich bei meinem nächsten Projekt so machen! :-)

> Ohnehin könnte man darüber diskutieren, ob man wirklich mit dem
> immerselben Präfix arbeiten oder sowas nicht raus-faktorisieren kann.

Das läuft eh über die Config-Datei und ist schlicht und ergreifend nötig,
weil man nicht weiß in was für einer Webserver-Umgebung das Projekt später
läuft und wenn man nur eine Datenbank zur Verfügung hat kann man ohne zwei
verschiedene Präfixe einfach nicht die stable und die beta Version eines
Projektes gleichzeitig laufen lassen (zumindest nicht in zwei verschiedenen
"Datenbanken").

Ciao

Alex

Re: Grundsaetzliches zu INDEX

Hallo Gott! Hallo Welt!

Ich habe mir gerade mal das Kapitel "7.4.5. Wie MySQL Indizes benutzt"
(http://dev.mysql.com/doc/refman/5.1/de/mysql-indexes.html) durchgelesen.
Und ich bekomme den Eindruck, das ich, wenn ich über jede Spalte die ich
für JOINs, WHEREs, ORDERs und GROUP BYs nutze einen INDEX lege, bald in
jeder meiner Tabellen die Hälfte der Spalten indiziere. (Ich dachte bisher
übrigens das MySQL sowie automatisch zahlreiche INDEXe pflegt und darum so
schnell ist.) Ich habe gelesen, das maximal 16 INDEXe pro Tabelle definiert
sein dürfen. Hat das irgendwelche Nachteile, wenn ich wirklich jede Spalte
die ich für JOINs, WHEREs, ORDERs und GROUP BYs nutze einen INDEX lege,
außer das die Datenbank dadurch größer wird (aber dafür schneller)?

Ciao

Alex

Re: Grundsaetzliches zu INDEX

Alexander Vipach wrote:

> Hat das irgendwelche Nachteile, wenn ich wirklich jede Spalte
> die ich für JOINs, WHEREs, ORDERs und GROUP BYs nutze einen INDEX lege,
> außer das die Datenbank dadurch größer wird (aber dafür schneller)?

Ja, jeder INSERT/UPDATE/DELETE wird viiieeel langsamer :-)

Dieter

Re: Grundsaetzliches zu INDEX

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)