MySQL Query Optimization – innere Abfragen

Das ist die ganze Frage …

SELECT s.*, (SELECT url FROM show_medias WHERE show_id = s.id AND is_primary = 1) AS media_url FROM (shows As s) WHERE `s`.`id` IN ( SELECT DISTINCT st.show_id FROM show_time_schedules AS sts LEFT JOIN show_times AS st ON st.id = sts.show_time_id WHERE sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date) ) AND `s`.`is_active` = 1 ORDER BY s.name asc 

Ob…

 SELECT url FROM show_medias WHERE show_id = s.id AND is_primary = 1 (0.0004 sec) 

Und…

  SELECT DISTINCT st.show_id FROM show_time_schedules AS sts LEFT JOIN show_times AS st ON st.id = sts.show_time_id WHERE sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date) (0.0061 sec) 

Gibt es einen offensichtlichen Grund ….

 SELECT s.*, (inner query 1) AS media_url FROM (shows As s) WHERE `s`.`id` IN ( inner query 2 ) AND `s`.`is_active` = 1 ORDER BY s.name asc 

dauert 5.7245 sec ?

ERKLÄRUNG ERWEITERT

 id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY s ALL NULL NULL NULL NULL 151 100.00 Using where; Using filesort 3 DEPENDENT SUBQUERY sts ALL NULL NULL NULL NULL 26290 100.00 Using where; Using temporary 3 DEPENDENT SUBQUERY st eq_ref PRIMARY PRIMARY 4 bvcdb.sts.show_time_id 1 100.00 Using where 2 DEPENDENT SUBQUERY show_medias ALL NULL NULL NULL NULL 159 100.00 Using where 

Solutions Collecting From Web of "MySQL Query Optimization – innere Abfragen"

Sie können immer EXPLAIN oder EXPLAIN EXTENDED verwenden, um zu sehen, was MySql mit einer Abfrage macht

Du könntest deine Anfrage auch etwas anders schreiben, hast du folgendes probiert?

 SELECT s.*, sm.url AS media_url FROM shows AS s INNER JOIN show_medias AS sm ON s.id = SM.show_id WHERE `s`.`id` IN ( SELECT DISTINCT st.show_id FROM show_time_schedules AS sts LEFT JOIN show_times AS st ON st.id = sts.show_time_id WHERE sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date) ) AND `s`.`is_active` = 1 AND sm.is_primary = 1 ORDER BY s.name asc 

Es wäre interessant zu sehen, was das bewirkt. Ich würde erwarten, dass es schneller ist, da ich derzeit denke, dass MySql für jede Show, die Sie haben, innere Abfrage 1 ausführen wird (so dass eine Abfrage viele Male ausgeführt wird. Eine Verknüpfung sollte effizienter sein.)

Ersetzen Sie den INNER JOIN durch einen LINKEN JOIN, wenn Sie alle Shows haben möchten, die in show_medias keine Zeile haben.

BEARBEITEN:

Ich werde kurz auf EXPLAIN EXTENDED schauen, ich frage mich auch, ob Sie folgendes versuchen wollen; Es entfernt alle Unterabfragen:

 SELECT DISTINCT s.*, sm.url AS media_url FROM shows AS s INNER JOIN show_medias AS sm ON s.id = SM.show_id INNER JOIN show_times AS st ON (s.id = st.show_id) RIGHT JOIN show_time_schedules AS sts ON (st.id = sts.show_time_id) WHERE `s`.`is_active` = 1 AND sm.is_primary = 1 AND sts.schedule_date BETWEEN CAST('2012-01-10' AS date) AND CAST('2012-01-14' AS date) ORDER BY s.name asc 

(Es wäre auch gut, die EXPLAIN EXTENDED auf diesen zu sehen – Sie könnten es zu den Kommentaren für diese hinzufügen).

Weiter EDIT:

Auf Ihrem EXPLAIN EXTENDED ( ein guter Anfang auf, wie man das hier liest )

Die Verwendung von USING FILESORT und TEMPORARY sind Schlüsselindikatoren. Hoffentlich sollte die zweite Abfrage, die ich empfehle, alle TEMPORARY-Tabellen (in der Unterabfrage) entfernen. Versuchen Sie dann, ORDER BY zu deaktivieren, um zu sehen, ob das einen Unterschied macht (und wir können das zu den bisherigen Ergebnissen hinzufügen 🙂

Ich kann auch sehen, dass die Abfrage bei vielen Index-Lookups möglicherweise fehlt; Alle Ihre ID-Spalten sind Hauptkandidaten für Indexübereinstimmungen (mit den üblichen Indexvorbehalten ). Ich würde auch versuchen, diese Indizes hinzuzufügen und dann erneut EXPLAIN EXTENDED auszuführen, um zu sehen, was der Unterschied jetzt ist (BEARBEITEN, wie wir bereits von Ihrem Kommentar oben wissen!)

Hier kommt die CTE-Lösung: (meine schlechte, mysql hat keine CTE's, aber das Problem ist zu allgemein)

 WITH RECURSIVE tree AS ( SELECT t0.id , t0.study_start_time , t0.study_end_time FROM tab t0 WHERE NOT EXISTS (SELECT * FROM tab nx WHERE nx.id=t0.id AND nx.study_end_time = t0.study_start_time ) UNION SELECT tt.id ,tt.study_start_time ,t1.study_end_time FROM tab t1 JOIN tree tt ON t1.id=tt.id AND t1.study_start_time = tt.study_end_time ) SELECT * FROM tree WHERE NOT EXISTS (SELECT * FROM tab nx WHERE nx.id=tree.id AND tree.study_end_time = nx.study_start_time ) ORDER BY id ; 

Ergebnisse:

 CREATE TABLE INSERT 0 15 id | study_start_time | study_end_time ------+------------------+---------------- 1234 | 168 | 480 2345 | 175 | 233 2345 | 400 | 425 4567 | 200 | 225 4567 | 250 | 289 4567 | 300 | 310 4567 | 320 | 340 4567 | 360 | 390 (8 rows) 

QUERY-Plan (nach dem Hinzufügen von offensichtlichem PK und Index):

 DROP TABLE NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tab_pkey" for table "tab" CREATE TABLE CREATE INDEX INSERT 0 15 QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Merge Anti Join (cost=16209.59..16292.13 rows=6386 width=12) (actual time=0.189..0.193 rows=8 loops=1) Merge Cond: ((tree.id = nx.id) AND (tree.study_end_time = nx.study_start_time)) CTE tree -> Recursive Union (cost=0.00..15348.09 rows=8515 width=12) (actual time=0.022..0.136 rows=15 loops=1) -> Merge Anti Join (cost=0.00..175.04 rows=1455 width=12) (actual time=0.019..0.041 rows=8 loops=1) Merge Cond: ((t0.id = nx.id) AND (t0.study_start_time = nx.study_end_time)) -> Index Scan using tab_pkey on tab t0 (cost=0.00..77.35 rows=1940 width=12) (actual time=0.010..0.018 rows=15 loops=1) -> Index Scan using sssss on tab nx (cost=0.00..77.35 rows=1940 width=8) (actual time=0.003..0.008 rows=14 loops=1) -> Merge Join (cost=1297.04..1500.28 rows=706 width=12) (actual time=0.010..0.012 rows=1 loops=6) Merge Cond: ((t1.id = tt.id) AND (t1.study_start_time = tt.study_end_time)) -> Index Scan using tab_pkey on tab t1 (cost=0.00..77.35 rows=1940 width=12) (actual time=0.001..0.004 rows=9 loops=6) -> Sort (cost=1297.04..1333.42 rows=14550 width=12) (actual time=0.006..0.006 rows=2 loops=6) Sort Key: tt.id, tt.study_end_time Sort Method: quicksort Memory: 25kB -> WorkTable Scan on tree tt (cost=0.00..291.00 rows=14550 width=12) (actual time=0.000..0.001 rows=2 loops=6) -> Sort (cost=726.15..747.44 rows=8515 width=12) (actual time=0.166..0.169 rows=15 loops=1) Sort Key: tree.id, tree.study_end_time Sort Method: quicksort Memory: 25kB -> CTE Scan on tree (cost=0.00..170.30 rows=8515 width=12) (actual time=0.025..0.149 rows=15 loops=1) -> Sort (cost=135.34..140.19 rows=1940 width=8) (actual time=0.018..0.018 rows=15 loops=1) Sort Key: nx.id, nx.study_start_time Sort Method: quicksort Memory: 25kB -> Seq Scan on tab nx (cost=0.00..29.40 rows=1940 width=8) (actual time=0.003..0.004 rows=15 loops=1) Total runtime: 0.454 ms (24 rows)