/MySQL: JOIN optimieren
0 0 0 
dedlfix
/MySQL: JOIN optimieren
Ich bräuchte etwas Hilfe beim optimieren eines Querys, der sehr lange läuft (~30 Sekunden). Folgende Situation - ich habe eine Tabelle userTitel und eine Tabelle titel. Nun führe ich folgenden Query aus:
SELECT titel.titelUrl, playcount, score, laenge FROM userTitel LEFT JOIN titel ON titel.titelUrl = userTitel.titelUrl WHERE userName = 'XXXX'
Wie gesagt, dieser läuft ungefähr 30 Sekunden (Anfragen auf einer Tabelle alleine - also ohne JOIN - brauchen etwa 0,2 Sekunden). Nun sind beides recht große Tabellen, die Frage ist ob sich da trotzdem noch etwas optimieren lässt. Die Result-Sets sind im Schnitt etwa 4000 Ergebnisse groß - da ich ja auf der "linken"-Tabelle selektiere müssen auch nur 4000 Joins durchgeführt werden.
Also, eure Meinung: Kann man an der Laufzeit noch was machen? Oder sind die 30 Sekunden eine realistische Laufzeit auf einem halbwegs aktuellen Desktop-Rechner?
Details zu den Tabellen:
mysql> DESC titel;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| titelUrl | varchar(255) | NO | PRI | NULL | |
| titelName | varchar(255) | NO | | NULL | |
| artistUrl | varchar(255) | NO | MUL | NULL | |
| laenge | mediumint(9) | NO | | NULL | |
| tagSkip | enum('0','1') | NO | | NULL | |
+-----------+---------------+------+-----+---------+-------+
(etwa 2,7 Millionen Einträge)
mysql> DESC userTitel;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| userName | varchar(255) | NO | MUL | NULL | |
| titelUrl | varchar(255) | NO | MUL | NULL | |
| playcount | int(11) | NO | | NULL | |
| score | float | NO | | NULL | |
+-----------+--------------+------+-----+---------+-------+
(etwa 24 Millionen Einträge)
mysql> EXPLAIN SELECT titel.titelUrl, playcount, score, laenge FROM userTitel LEFT JOIN titel ON titel.titelUrl = userTitel.titelUrl WHERE userTitel.userName = 'XXXX';
+----+-------------+-----------+--------+---------------+----------+---------+---------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+---------------+----------+---------+---------------------------------+------+-------------+
| 1 | SIMPLE | userTitel | ref | userName | userName | 767 | const | 9160 | Using where |
| 1 | SIMPLE | titel | eq_ref | PRIMARY | PRIMARY | 767 | diplomarbeit.userTitel.titelUrl | 1 | |
+----+-------------+-----------+--------+---------------+----------+---------+---------------------------------+------+-------------+
mysql> show profile for query 3;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| starting | 0.000052 |
| checking query cache for query | 0.000305 |
| Opening tables | 0.000038 |
| System lock | 0.000010 |
| Table lock | 0.000113 |
| init | 0.000076 |
| optimizing | 0.000035 |
| statistics | 0.055601 |
| preparing | 0.000080 |
| executing | 0.000008 |
| Sending data | 27.786912 |
| end | 0.000025 |
| query end | 0.000005 |
| freeing items | 0.000083 |
| storing result in query cache | 0.000010 |
| logging slow query | 0.000003 |
| logging slow query | 0.011079 |
| cleaning up | 0.000026 |
+--------------------------------+-----------+
(was genau ist "sending data" - es hat jedenfalls nichts mit der Größe der zurückgelieferten Daten zu tun)
-
da ich ja auf der "linken"-Tabelle selektiere müssen auch nur 4000 Joins durchgeführt werden.
Das ist ein Irrtum. Es werden sämtliche Sätze ver-joint, denn die WHERE-KLausel könnte sich ja auf eine (erst durch den JOIN erzeugte) Kombination beziehen.
Da solltest dich über Subselects schlau machen oder zwei SQLs nehmen.
-
da ich ja auf der "linken"-Tabelle selektiere müssen auch nur 4000 Joins durchgeführt werden.
Das ist ein Irrtum. Es werden sämtliche Sätze ver-joint, denn die WHERE-KLausel könnte sich ja auf eine (erst durch den JOIN erzeugte) Kombination beziehen.
Soweit ich das sehe, ist MySQL so clever das zu erkennen. Denn aus der WHERE-Bedingung geht ja hervor, dass nur aus der linken Seite selektiert wird. ich habe mal den Test gemacht und in die WHERE-Bedingung einen nicht existierenden Usernamen eingesetzt (bzw. einen der nur sehr selten vorkommt). Das hat den Query extrem beschleuningt. Es scheint also in der Tat so zu sein, das nur die nötigen Zeilen gejoined werden - sonst würde die Laufzeit ja nicht von der Größe des Ergebnisses abhängen.
-
-
Mahlzeit AllesMeins,
SELECT titel.titelUrl, playcount, score, laenge FROM userTitel LEFT JOIN titel ON titel.titelUrl = userTitel.titelUrl WHERE userName = 'XXXX'
Du könntest dafür sorgen, dass die für die Einschränkungen verwendeten Spalten (in diesem Fall titel.titelUrl, userTitel.titelUrl und userTitel.userName) mit vernünftigen Indizes versehen sind, um die Suche zu beschleunigen.
MfG,
EKKi--
sh:( fo:| ch:? rl:( br:> n4:~ ie:% mo:} va:) de:] zu:) fl:{ ss:) ls:& js:|-
Hi!
SELECT titel.titelUrl, playcount, score, laenge FROM userTitel LEFT JOIN titel ON titel.titelUrl = userTitel.titelUrl WHERE userName = 'XXXX'
Du könntest dafür sorgen, dass die für die Einschränkungen verwendeten Spalten (in diesem Fall titel.titelUrl, userTitel.titelUrl und userTitel.userName) mit vernünftigen Indizes versehen sind, um die Suche zu beschleunigen.In Indexe dachte ich auch zuerst, aber dann sah ich weiter unten, dass welche vorhanden sind und laut EXPLAIN auch verwendet werden. Für userTitel.titelUrl braucht es für diese Abfrage keinen Index. userTitel steht auf der linken Seite, was heißt, dass beim Left Join alle Datensätze genommen werden. Die passenden dazu müssen aus titel gesucht werden, und auf titel.titelUrl liegt der Primärindex, der laut EXPLAIN auch verwendet wird. Für die Einschränkung mit der WHERE-Klausel wird userTitel.userName verwendet. Dieses Feld hat einen Index, der ebenfalls verwendet wird.
Und nun? Meine Gedankengang ist, dass die WHERE-Klausel erst nach der Erstellung des Join-Produkts ausgewertet wird, was bei der Menge der Datensätze die Zeit verbraucht. Allerdings könnte vielleicht auch der eingebaute Optimierer so clever sein, sich lediglich auf die geWHEREten Datensätze zu beschänken. Meine Untersuchen wären nun wie folgt: Lediglich zum Schauen, ob das Punkte bringt, würde ich die Bedingung im WHERE in die Join-Bedingung verlagern.
ON titel.titelUrl = userTitel.titelUrl AND userName = 'XXXX'
Hier vermute ich, dass sich dadurch das Join-Produkt nur auf die XXXX-Datensätze beschränkt.
Da aus der Tabelle titel zu jedem userTitel-Datensatz nur (keine oder) eine Zeile kommen kann, wäre der nächste Test die schon vorgeschlagene korrelierte Subquery-Version. Wenn es - wie in der gezeigten Abfrage - lediglich um das Feld laenge aus titel geht, würde ich die Subquery nach diesem Feld in die SELECT-Klausel stellen. Wenn noch weitere Felder benötigt werden, wird es aber recht aufwendig, dafür je eine eigene Subquery zu notieren. Dann kommt man mit einem Join günstiger. Allerdings müsste man dann untersuchen, wenn mein erster Vorschlag nichts bringt, wie man die zu joinende Datenmenge einschränken kann. Vielleicht so:
SELECT felder FROM (SELECT * FROM userTitel WHERE userName = 'XXXX') AS ut LEFT JOIN titel ...
Damit erhoffe ich mir, dass die Join-Menge auf den gewünschten User beschränkt wird.
Lo!
-
Hi,
danke für die Vorschläge!
Allerdings könnte vielleicht auch der eingebaute Optimierer so clever sein, sich lediglich auf die geWHEREten Datensätze zu beschänken.
So scheint es zu sein, habe mal ein paar Tests gemacht, indem ich seltene oder gar nicht existierende Usernamen eingesetzt habe. Das hat den Query sehr beschleunigt (bis hin zu 0 Sekunden Laufzeit, wenn der Username gar nicht existiert hat)
ON titel.titelUrl = userTitel.titelUrl AND userName = 'XXXX'
Das hat genau den gegenteiliogen Effekt gehabt, die Laufzeit war absolut jenseits von gut und böse... Habe den Query nach 7 Minuten abgebrochen
SELECT felder FROM (SELECT * FROM userTitel WHERE userName = 'XXXX') AS ut LEFT JOIN titel ...
Das funktioniert besser. Damit läuft der Query noch etwa 5 Sekunden - das ist zwar auch nicht optimal, aber schonmal viel besser. Danke!
Marc
-
moin,
Für die Einschränkung mit der WHERE-Klausel wird userTitel.userName verwendet. Dieses Feld hat einen Index, der ebenfalls verwendet wird.
man könnte es noch mal mit einem zusammengesetzen index (userName, titelUrl) versuchen und schauen, wie das dbms darauf reagiert. was mich viel mehr interessieren würde, ob die beteiligten objekte den auch alle analysiert sind.
Meine Untersuchen wären nun wie folgt: Lediglich zum Schauen, ob das Punkte bringt, würde ich die Bedingung im WHERE in die Join-Bedingung verlagern.
ON titel.titelUrl = userTitel.titelUrl AND userName = 'XXXX'
das geht gar nicht, das führt zu einem ganz anderem ergebnis. damit würden alle datensätze aus der "userTitel" tabelle genommen werden, egal welchen eintrag sie im userName haben !
das andere was mich interessieren würde ist, was er fachlich überhaupt haben will, bevor ich mich ran machen würde, die query zu optimieren. es könnte sein, dass dabei wesentlich mehr potential steckt.
Ilja
-
-