SELECT DISTINCT sehr langsam

Aya · Verfasst: Mi 20.02.13 12:18

Hi,

ich habe in einem Programm hier einen riesen MySQL Query der in der Ausführung 90sek dauert.. auf der Suche nach der Ursache habe ich das ganze auf ein paar Zeilen zusammen gekürzt die das Problem sehr verdeutlichen:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

SELECT DISTINCT

f.`version`,
tvs.`id_task_status`

FROM `file` f
LEFT JOIN `file_task_connection` ftc USING(`id_file`)
LEFT JOIN `task_version_status` tvs USING(`id_task`, `version`)

WHERE f.`id_project` = 56 AND f.`id_file_master` IS NOT NULL

Diese Abfrage dauert 17sek (file und file_task_connection haben jeweils etwa 700.000 Einträge, task_version_status hat 50.000).

Auch ist auf allen Feldern die in dem Query vorkommen ein Index (BTREE).

Der Query liefert etwa 600 Ergebnisse, wenn ich das DISTINCT wegnehme bekomme ich 200.000 Stück, allerdings dauert der Query dann auch nur 0.2 Sekunden statt 17..

Wenn ich das ganze mit EXPLAIN mir anschaue bekomme ich:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:

+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+------------------------------+
| id | select_type | table | type | possible_keys                         | key                        | key_len | ref                    | rows   | Extra                        |
+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+------------------------------+
|  1 | SIMPLE      | f     | ref  | fk_file_project,fk_file_file_master   | fk_file_project            | 4       | const                  | 263232 | Using where; Using temporary |
|  1 | SIMPLE      | ftc   | ref  | PRIMARY,fk_file_has_task_file1        | PRIMARY                    | 4       | vfxdb_v001.f.id_file   |      1 | Using index                  |
|  1 | SIMPLE      | tvs   | ref  | fk_taskVersionStatus_task1,SpeedIndex | fk_taskVersionStatus_task1 | 5       | vfxdb_v001.ftc.id_task |     11 |                              |
+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+------------------------------+

Ohne DISTINCT und mit EXPLAIN:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:

+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+-------------+
| id | select_type | table | type | possible_keys                         | key                        | key_len | ref                    | rows   | Extra       |
+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+-------------+
|  1 | SIMPLE      | f     | ref  | fk_file_project,fk_file_file_master   | fk_file_project            | 4       | const                  | 263232 | Using where |
|  1 | SIMPLE      | ftc   | ref  | PRIMARY,fk_file_has_task_file1        | PRIMARY                    | 4       | vfxdb_v001.f.id_file   |      1 | Using index |
|  1 | SIMPLE      | tvs   | ref  | fk_taskVersionStatus_task1,SpeedIndex | fk_taskVersionStatus_task1 | 5       | vfxdb_v001.ftc.id_task |     11 |             |
+----+-------------+-------+------+---------------------------------------+----------------------------+---------+------------------------+--------+-------------+

Gibt es da jetzt irgendeine Möglichkeit das ganze schneller zu bekommen?

Danke~
Aya

PS: Es sind alles InnoDBs

_________________
Aya
I aim for my endless dreams and I know they will come true!

Ralf Jansen · Verfasst: Mi 20.02.13 12:26

Zitat:

Der Query liefert etwa 600 Ergebnisse, wenn ich das DISTINCT wegnehme bekomme ich 200.000 Stück, allerdings dauert der Query dann auch nur 0.2 Sekunden statt 17..

Bist du dir bei den 0.2 Sekunden sicher? Oder ist es eher so das die Query bei dir anfängt nach 0.2 Sekunden Daten zu liefern bis du alle 200000 hast aber dann doch ähnlich lang dauert wie ohne Distinct.

Laut EXPLAINPLAN ist ja der einzige Unterschied das die 200000 Datensätze durch ein Temporäre Tabelle gejagt werden um dort die Duplikate zu entsorgen. Mit meinen spärlichen MYSQL Kenntnissen würde ich das so interpretieren das beide Abfragen ähnlich lang dauern sollten du mit DISTINCT aber einfach nur später den ersten Datensatz bekommst.

Aya · Verfasst: Mi 20.02.13 12:31

Die 0.2sek sind nur die Zeit bis der Query ausgeführt wurde, ja. Hab es eben mal mit einer Stoppuhr getestet, die zeit von der Anfrage bis dahin wo alle Daten gelesen wurden ist bei beiden in etwa gleich..

Also gibt es da wohl nur recht wenig zu optimieren?

_________________
Aya
I aim for my endless dreams and I know they will come true!

WasWeißDennIch · Beiträge: 653 Erhaltene Danke: 160

Hast Du mal versucht, das DISTINCT durch eine Gruppierung zu ersetzen?

Aya · Verfasst: Mi 20.02.13 12:41

Jep, dauert leider genau gleich lang

_________________
Aya
I aim for my endless dreams and I know they will come true!

Aya · Verfasst: Mi 20.02.13 13:05

Hab noch eine Sache getestet, wenn ich den letzten JOIN rausnehme, dann dauert es (inkl. holen der Datensätze) 1.2 Sekunden.

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

SELECT DISTINCT

f.`version`
#,tvs.`id_task_status`

FROM `file` f
LEFT JOIN `file_task_connection` ftc USING(`id_file`)
#LEFT JOIN `task_version_status` tvs USING(`id_task`, `version`)

WHERE f.`id_project` = 56 AND f.`id_file_master` IS NOT NULL

Hier das dazugehörige EXPLAIN:

markieren

SQL-Anweisung

1:
2:
3:
4:
5:
6:

+----+-------------+-------+------+-------------------------------------+-----------------+---------+----------------------+--------+-------------+
| id | select_type | table | type | possible_keys                       | key             | key_len | ref                  | rows   | Extra       |
+----+-------------+-------+------+-------------------------------------+-----------------+---------+----------------------+--------+-------------+
|  1 | SIMPLE      | f     | ref  | fk_file_project,fk_file_file_master | fk_file_project | 4       | const                | 263232 | Using where |
|  1 | SIMPLE      | ftc   | ref  | PRIMARY,fk_file_has_task_file1      | PRIMARY         | 4       | vfxdb_v001.f.id_file |      1 | Using index |
+----+-------------+-------+------+-------------------------------------+-----------------+---------+----------------------+--------+-------------+

Wieso macht es der eine JOIN so sehr viel langsamer?

Aya~

_________________
Aya
I aim for my endless dreams and I know they will come true!

Ralf Jansen · Verfasst: Mi 20.02.13 13:05

Beschleunigen würde wahrscheinlich nur wenn du es schaffst die Duplikate bereits während der Join Operation loszuwerden. Alles was danach kommt, also wenn die 200000 Datensätze schon existieren, wird wohl nichts ändern. Da können wir aber wohl wenig helfen dafür muß man inneres Wissen über die Datenbankstruktur haben um eventuell geschickt die Reihenfolge der Joins festzulegen oder Subselect mit Teiljoins einzuziehen um schon einen ~Distinct~ frühzeitig zu platzieren bevor sich das Ergebnis auf entsprechend viele Datensätze multipliziert hat.

Ist der Left Join auf file_task_connection ein Überbleibsel der Kürzung? In der gekürzten Fassung sieht der Überflüssig aus.

Edit:

Zitat:

Wieso macht es der eine JOIN so sehr viel langsamer?

Es wird keine Temporäre Tabelle verwendet also scheint in dieser Form der Distinct keinen Unterschied zu machen was der SQl Optimizer gemerkt hat und dir wieder frühzeitig die erste Row liefern kann.

Aya · Verfasst: Mi 20.02.13 13:11

Ralf Jansen hat folgendes geschrieben :

Ist der Left Join auf file_task_connection ein Überbleibsel der Kürzung? In der gekürzten Fassung sieht der Überflüssig aus.

Die file_task_connection ist eine Tabelle mit nur zwei Spalten: id_file und id_task. In der File Tabelle selbst gibt es kein id_task, daher wird die für das spätere JOIN mit der task_version_status tabelle benötigt

Ralf Jansen hat folgendes geschrieben :

Es wird keine Temporäre Tabelle verwendet also scheint in dieser Form der Distinct keinen Unterschied zu machen was der SQl Optimizer gemerkt hat und dir wieder frühzeitig die erste Row liefern kann.

In dem Falle werden aber auch knapp 200.000 Zeilen zurückgegeben, und das abholen dieser ist in den 1.2 Sek schon drin - wohingegen vorher bei 200.000 Zeilen es 17 Sek gedauert hat sie zu holen..

_________________
Aya
I aim for my endless dreams and I know they will come true!

Ralf Jansen · Verfasst: Mi 20.02.13 13:20

Dann liegt es vielleicht dran das der letzte LEFT JOIN auch irgendwie überflüssig geworden ist und die Join Operation nicht mehr ausgeführt wird.
Für mich entspricht dein SQL jetzt

markieren

SQL-Anweisung

1:
2:
3:

SELECT f.`version` 
  FROM `file` f
 WHERE f.`id_project` = 56 AND f.`id_file_master` IS NOT NULL

weil nix mit ftc gemacht wird bzw. alles was an Daten daraus gezogen wurde durch das Distinct wieder weg ist. Mein EXPLAIN Lesefähigkeiten geben aber leider nicht her ob der Optimizer so schlau war und das intern so umgesetzt hat.

	Mitgliederliste
	Gruppen
	Das Team
	Richtlinien
	Synonyme