DISTINCT über mehrere Spalten

[Forumsgast]

Hi,

wenn ich bei rund 260.000 Datensätzen folgende MySQL Abfrage starte

SELECT DISTINCT y,company FROM `all_data` WHERE y IN (2014,2015,2016,2017) ORDER BY y

dann dauert das Ganze ca. 3,3 Sekunden.

Wenn ich jedoch mehrere Abfragen mache:

foreach ($years as $y) {
...
"SELECT DISTINCT company FROM `all_data` WHERE y = ".$y
...
} 

dauert der Vorgang zwei Zehntelsekunden.

Nun soll man ja keine SQL Abfragen in Schleifen machen, hier scheint mir das aber mal angezeigt,
oder gibt es da eine performantere Catch-All-SQL Variante?

[Forumsgast]

Was sagt denn explain?

[Forumsgast]

Sonst teste doch mal ohne Sortierung, ich könnte mir vorstellen, dass das durch die Kombi IN und ORDER BY kommt.

[Forumsgast]

Ja, Sortierung fehlt im 2. Statement. Wenn das Ergebnis groß ist, macht es natürlich einen Unterschied.
Andere Frage:
Welchen Typ hat Spalte Y? Falls es von den Zahlenangaben der In Clause abweicht, wäre eine Anpassung sicher hilfreich.
Ansonsten auch mal mit den Werten spielen und schauen, wo es kippt: In-Clause mit nur einem Wert, zwei, drei..

Alltagserfahrung: Die Nutzung der In-Clause ist meist relativ schlecht optimiert. Sie kann sowieso immer umformuliert, also vermieden werden, sie ist eigentlich nur in der Fomulierung bequem.

P.S.: Im 2. Statement muss natürlich auch eine deutlich kleinere Menge DISTINCTed werden. Vielleicht sprengst Du da zufällig eine Memory-Grenze, wo die Engine das Ergebnis nicht mehr im RAM bearbeitet, sondern auf Platte umsteigt.

[Forumsgast]

Hier mein Testergebnis aus 500000 Datensätzen. ohne Index auf y

SELECT DISTINCT `y` , `vorname`
FROM `mytable`
WHERE `y`
IN ( 1985, 1986, 1987, 1988 )
ORDER BY `y` 

PHPMyAdmin zeigt
Showing rows 0 - 29 ( 7,841 total, Query took 0.5625 sec) [y: 1985 - 1985]

Das Ergebnis ändert sich nicht wenn man OR statt IN anwendet, aber es wird viel schneller wenn man Order By weg lässt.
Distinct hat nur wenig Einfluss gezeigt, aber auch wenn ich die Funktion YEAR verwendet habe bin ich nicht über 1.5 Sekunden gekommen, da muss also noch was anderes bremsen..

[Forumsgast]

Vielleicht auch mal IN gegen BETWEEN tauschen. Ist das vielleicht besser optimiert bei MySQL?

[Forumsgast]

Vielen Dank an alle für die schnellen wertvollen Hinweise.

Arne Drews : Stimmt, ohne ORDER BY fluppt das Ganze viel schneller. Also im 0,0x Bereich. Das ORDER BY brauche ich auch tatsächlich nicht unbedingt... Insofern bin ich schon mal weiter.

Erstaunlich ist nur, dass bei 25 Ergebnis-Datensätzen ein ORDER BY soviel kostet.

Das EXPLAIN ist angefügt.

[Forumsgast]

ich würde aus dem Explain eher entnehmen, daß da 257344 Rows via filesort sortiert werden, nicht nur 25. Aber was weiß ich schon von MySQL ...

[Forumsgast]

Aber was weiß ich schon von MySQL ...

Ja nix...

[Forumsgast]

ich würde aus dem Explain eher entnehmen, daß da 257344 Rows via filesort sortiert werden, nicht nur 25. Aber was weiß ich schon von MySQL ...

Nicht ganz. Wieviel Datensätze sortiert werden ist aus dem Explain nicht ersichtlich. Was das Explain sagt ist, die Datenbank geht über 257344 Datensätze, schreibt die Treffer in eine temporärer Tabelle und sortiert die Tabelle. Je nach selektivität der WHERE Klausel sind das mehr oder weniger Datensätze. In dem Fall dürften das aber eine ganze Menge sein.
Warum das Mysql macht? Weil der Optimizer manchmal blöd ist. DISTINCT + ORDER BY werden zu GROUP BY umgeschreiben. Das GROUP BY wiederrum, wird in diesem Fall, über eine Sortierung der zu gruppierenden Daten aufgelöst. Bei x hundertausend Datensäzten, die auf 25 Ergebnisse zusammenschrumpfen, ist das extrem ungünstig.

[Forumsgast]

Tja, Optimizer halt.
Ich kann das Verhalten allerdings nicht nachvollziehen. Die Angabe der Spaltentypen sowie Indizierung, mglw auch Version und Memorykonfiguration wären empfehlenswert.
Ebenso eine genauere Häufigkeitsverteilung: 25 Ergebnis-Datensätze bei welchem Statement, welche Parameter?

Vielleicht ist es auch vergebliche Mühe, weil mysql immer stumpf gleich "optimiert", trotzdem zeigen meine Tests ein anderes, unauffälliges Verhalten.

[Forumsgast]

MySQL halt. EXPLAIN ANALYSE in PG ist Lichtjahre besser.

[Forumsgast]

akretschmer das hat hier auch niemand angezweifelt

[Forumsgast]

Optimizer halt...

MySQL halt...

Dass er diesen Marketingreflex hat, tja, ...
Ich kenne nur 2 gute Optimizer.

Interessant ist doch, dass bei derartigen Problemen der Optimizer gern vergessen wird. Oder eher noch, die Auswertung des Query Plans und der Index Existenz bzw. Index Nutzung. Relevant wird das natürlich erst bei vielleicht 6 stelligen Datensatzzahlen (ok, die berühmte If Schleife gibt es auch noch).

Also öfter mal ein EXPLAIN oder EXPLAIN ANALYSE einstreuen, kann sehr hilfreich sein.