Alternative mit UNION ALL

[Rolf B]

Hallo Raketenwilli,

ich wollte Dir schon ein + geben für die clevere Idee, aber da ist das MYSQL Handbuch vor:

Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows.

Als ich SQL lernte - was zugegebenermaßen Jahrzehnte her ist - war ORDER BY für den Input eines Set-Operators (UNION & Co) nicht erlaubt.

Ein ORDER BY, habe ich gelernt, ist nur auf das Gesamtergebnis nach dem UNION anwendbar. Deswegen hab ich gerade ins MySQL Handbuch geschaut, nicht weil ich Dir misstraute, sondern eher mit dem "boah ey, was geht denn noch alles in MYSQL was eigentlich verboten ist". Aber dann fand ich den genannten Hinweis. Wenn dein Vorschlag Berlin an's Ende bringt, dann ist es Zufall und kein definiertes Ergebnis.

Darüber hinaus wird dein Vorschlag vom MySQL Handbuch mit dem Hinweis versehen, dass man einen Teil-Select, der LIMIT oder ORDER BY nutzen soll, einklammern muss. Vor MySQL 5.7 ging's wohl ohne, ab 5.7 ist es Pflicht. Und was die Robbe Maria dazu sagt, ist vermutlich noch eine weitere Frage.

Und noch ein Zitat aus dem Handbuch:

To cause rows in a UNION result to consist of the sets of rows retrieved by each SELECT one after the other, select an additional column in each SELECT to use as a sort column and add an ORDER BY that sorts on that column following the last SELECT:

Man müsste es vermutlich so aufschreiben, um den Zufall auszuschließen und keinen Syntaxerror zu riskieren. Ich habe es nicht ausprobiert; ich habe auch kein MYSQL 5.7 hier, deswegen weiß ich nicht ob noch 1-2 Table Aliases nötig sind.

( SELECT 1 as group_number, *
  FROM city_germany 
  WHERE city_name != "Berlin"
  ORDER BY city_name)
UNION ALL
( SELECT 2 as groupNumber, * FROM city_germany
  WHERE city_name = "Berlin")
ORDER BY group_number, city_name

Ich fürchte nur, dass der Effizienzgewinn dann wieder verloren geht. Ein ORDER BY vor dem UNION ist laut MYSQL Handbuch nur sinnvoll, wenn man dazu auch LIMIT einsetzen will.

Ob der erste Teil dann einen Index nutzt oder einen Tablescan macht, hängt vom Optimizer ab. Indexsequenzielles Lesen eines Tablespace ist ineffizient, wenn er auf die Platte zugreifen muss (weil das zu viele Kopfbewegungen auf der Platte kostet). Wenn er erwartet, dass ein signifikanter Teil der Table im Ergebnis landet, bevorzugt er den Tablescan, weil die Daten dann im Stream von der Platte kommen können.

Es hängt aber auch an der Tablesize. Wenn alle data pages der Tabelle im Cache sind, optimiert er vielleicht nach und schaltet auf indexsequenziellen Zugriff um. Und ein gutes DBMS berücksichtigt auch noch, ob der Tablespace auf einer Magnetplatte oder einer SSD gespeichert ist. Wieder mal viel Spekulatius. Am Ende hilft nur messen messen messen, und die Query auf der Produktions-DB explainen lassen - um zu schauen, was der Optimizer im Realbetrieb draus macht. Die Test-DB ist nicht repräsentativ.

Rolf