sql ORDER, bestimmter Eintrag soll erst am Schluss angezeigt werden

[mARKUS]

Ich versuche meine Daten zu sortieren und auszugeben.

SELECT * FROM city_germany ORDER by city_name ASC

ich möchte aber das Berlin nicht in der Reihe ist, sondern am Ende ausgegeben wird. Ist das möglich ohne ein neues Feld zur Sortier-Reihenfolge anzulegen?

Markus

[Tabellenkalk]

Hallo,

ich möchte aber das Berlin nicht in der Reihe ist, sondern am Ende ausgegeben wird. Ist das möglich ohne ein neues Feld zur Sortier-Reihenfolge anzulegen?

Ja, schreib Berlin mit Z…
Oder häng irgendein anderes Zeichen vorne dran, das nach dem Alphabet sortiert wird.

Gruß
Kalk

[dedlfix]

Tach!

ich möchte aber das Berlin nicht in der Reihe ist, sondern am Ende ausgegeben wird. Ist das möglich ohne ein neues Feld zur Sortier-Reihenfolge anzulegen?

Ja, mit einem berechneten Sortierkriterium, das zuerst allem, was nicht Berlin ist, einen kleineren Wert gibt als Berlin. Das zweite Kriterium ist dann der Stadtname.

... ORDER BY IIF(city_name = 'Berlin', 1, 0), city_name

Beachte, dass bei solchen berechneten Ausdrücken ein eventuell vorhandener Index nicht oder nur eingeschränkt verwendet werden kann.

dedlfix.

[Rolf B]

Hallo dedlfix,

ja, mit dem Index muss man aufpassen, fleißig explainen und ggf. auch messen.

Ich habe hier eine Testtabelle mit 4000 Städtenamen - ein SELECT ORDER BY ohne WHERE macht einen Filesort, trotz Index auf den Namen. Weil das effizienter ist als ein Indexed Seek pro Satz. Aber 4000 Sätze mit 4 Spalten ist noch nicht viel, das sortiert er fast schon in den CPU Registern. Eine Monstertable habe ich nicht vorliegen.

Wenn ich auf LIKE M%' filtere, macht er einen Indexscan und gewinnt auch gleich die Order dadurch. Kommt die IF-Funktion dabei, wird aus dem ORDER BY ein Filesort.

Rolf

[Rolf B]

Hallo Markus,

(Edit: Verdammt, während ich ausprobiert habe, ob das überhaupt geht, schreibt Dedlfix schon meine Antwort hin 😂 - ich mach zu oft MS SQL statt MYSQL um auswendig zu wissen, was in welchem von den beiden funktioniert).

Oder häng irgendein anderes Zeichen vorne dran, das nach dem Alphabet sortiert wird.

Prinzipiell ist das der Weg, aber dafür muss man die Spalte city_name nicht manipulieren.

Verwende eine berechnete Spalte und sortiere danach. Bei MYSQL muss diese Spalte nicht mal Teil der SELECT Liste sein. Für die Berechnung verwende die IF Funktion.

SELECT *
FROM city_germany
ORDER BY IF(city_name='Berlin',1,0), city_name

Für Berlin liefert die IF-Funktion 1, sonst 0. Dadurch kommt Berlin an die letzte Stelle.

Rolf

[Raketenwilli]

Alternative ohne die Nachteile bezüglich der Indexierung:

SELECT *
FROM city_germany
WHERE city_name != "Berlin" ORDER BY city_name
UNION ALL
SELECT * FROM city_germany
WHERE city_name = "Berlin"

[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

[Raketenwilli]

(Die Tests habe ich mit MariaDB 10.5 und der öffentlich verfügbaren GN250-Datentabelle gemacht)

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.

Du hast Recht: Der Optimizer schmeisst das ORDER BY aus der Abfrage, es sei denn dieses ist mit LIMIT kombiniert:

  (SELECT
     NNID, Name
     FROM gn250
     WHERE Name !="Pilsdorf" ORDER BY Name
  )
UNION ALL
 (SELECT
     NNID, Name
     FROM gn250 where Name="Pilsdorf"
 )
LIMIT 10;

funktioniert NICHT, die Daten werden nicht sortiert („wie Kraut und Rüben“. Pilsdorf bleibt aber der letzte Eintrag.

Jetzt halte ich mich für ein nicht ganz doofes Kerlchen und denke mir: „Was? Du willst ein LIMIT? Du kriegst ein LIMIT!“. Ich weiß ja nach einer Zählung, dass die Datenbank 149745 Einträge hat und denke mir „Friss das hier“:

  (SELECT
     NNID, Name
     FROM gn250
     WHERE Name !="Pilsdorf" ORDER BY Name LIMIT 150000
  )
UNION ALL
 (SELECT
     NNID, Name
     FROM gn250 where Name="Pilsdorf"
 )

Das Ergebnis sieht gut aus (ist also wie erwartet sortiert). Bis auf den Umstand, das MariaDB wie folgt „nölt“:

149745 rows in set (0.554 sec)

Eine ganze halbe Sekunde? Das ist „viel“, weil das eine faktisch lastlose Datenbank auf einem sich totlachendem System ist.

Da wären also zwei getrennte Abfragen und die spätere Vereinigung des Ergebnisses z.B. im abfragenden PHP-Skript schneller…

EXPLAIN sagt dann für die Teilabfrage mit dem großen Ergebnis:

Using where; Using filesort. 

Der Vorteil des Nutzens des Indexes ist also weg.

Noch ein Nachtrag:

Auf die Idee, hilfsweise eine View zu bauen ...

CREATE VIEW gn250_by_name AS SELECT * FROM gn250 ORDER BY NAME;

und diese in der Abfrage zu benutzen bin ich auch gekommen:

Bringt nichts, Ergebnis wie im ersten Versuch, der Optimizer verwirft das ORDER. (Und das ohne Warnung oder Notiz!)

[Rolf B]

Hallo Raketenwilli,

danke für die weiteren Tests 😀

Ein SQL Optimizer ist nun einmal ein eigensinniger Geselle, dessen Reaktionen man nur nach einer langen Kennenlernphase voraussehen kann. Und auch dann überrascht er einen immer wieder.

Rolf

[Raketenwilli]

# Hilfspalte:
ALTER TABLE gn250 ADD COLUMN is_pilsdorf TINYINT;
### Query OK, 0 rows affected (0.008 sec)
### Records: 0  Duplicates: 0  Warnings: 0

# Füllen:
UPDATE gn250 SET is_pilsdorf=1 WHERE Name="Pilsdorf";
### Query OK, 1 row affected (0.006 sec)
### Rows matched: 1  Changed: 1  Warnings: 0

# Indexieren:
CREATE INDEX is_pilsdorf ON gn250 (is_pilsdorf);
### Query OK, 0 rows affected (0.190 sec)               
### Records: 0  Duplicates: 0  Warnings: 0

Abfrage:

SELECT NNID, Name FROM gn250 ORDER BY is_pilsdorf,Name
### (gewünschtes Ergebnis)

Aber: „Rakete geht irgendwie anders“:

149745 rows in set (0.565 sec)

Nachgeschaut:

Die Tabelle hat einen Index auf Name, einen auf is_pilsdorf (und weitere).

Aber:

explain SELECT NNID, Name FROM gn250 ORDER BY is_pilsdorf,Name;

+------+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+--------+----------------+
|    1 | SIMPLE      | gn250 | ALL  | NULL          | NULL | NULL    | NULL | 141636 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+--------+----------------+

Demnach wird also der Index nicht benutzt. Selbst wenn ich dessen Benutzung erzwingen will …

SELECT NNID, Name FROM gn250 USE INDEX (Name) USE INDEX (is_pilsdorf) ORDER BY is_pilsdorf,Name;

… wird das nicht anders.

Ist die Tabelle mit fast 150000 Zeilen etwa zu klein? Oder hab ich was falsch gemacht?

MariaDB [test]> describe gn250;

+-------------+----------------+------+-----+---------+-------+
| Field       | Type           | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+-------+
| NNID        | varchar(16)    | YES  | UNI | NULL    |       |
| DATUM       | date           | YES  | MUL | NULL    |       |
| OBA         | varchar(100)   | YES  | MUL | NULL    |       |
| OBA_WERT    | varchar(100)   | YES  | MUL | NULL    |       |
| NAME        | varchar(255)   | YES  | MUL | NULL    |       |
| SPRACHE     | varchar(100)   | YES  |     | NULL    |       |
| GENUS       | varchar(100)   | YES  |     | NULL    |       |
| NAME2       | varchar(100)   | YES  | MUL | NULL    |       |
| SPRACHE2    | varchar(100)   | YES  |     | NULL    |       |
| GENUS2      | varchar(100)   | YES  |     | NULL    |       |
| ZUSATZ      | varchar(100)   | YES  |     | NULL    |       |
| AGS         | bigint(20)     | YES  |     | NULL    |       |
| RS          | bigint(20)     | YES  |     | NULL    |       |
| HOEHE       | int(11)        | YES  |     | NULL    |       |
| HOEHE_GER   | int(11)        | YES  |     | NULL    |       |
| EWZ         | int(11)        | YES  |     | NULL    |       |
| EWZ_GER     | int(11)        | YES  |     | NULL    |       |
| GEWK        | varchar(100)   | YES  |     | NULL    |       |
| GEMTEIL     | tinyint(1)     | YES  |     | NULL    |       |
| VIRTUELL    | tinyint(1)     | YES  |     | NULL    |       |
| GEMEINDE    | varchar(100)   | YES  | MUL | NULL    |       |
| VERWGEM     | varchar(100)   | YES  | MUL | NULL    |       |
| KREIS       | varchar(100)   | YES  | MUL | NULL    |       |
| REGBEZIRK   | varchar(100)   | YES  | MUL | NULL    |       |
| BUNDESLAND  | varchar(100)   | YES  | MUL | NULL    |       |
| STAAT       | varchar(3)     | YES  | MUL | NULL    |       |
| GEOLA       | decimal(7,4)   | YES  | MUL | NULL    |       |
| GEOBR       | decimal(7,4)   | YES  | MUL | NULL    |       |
| GKRE        | decimal(20,10) | YES  |     | NULL    |       |
| GKHO        | decimal(20,10) | YES  |     | NULL    |       |
| UTMRE       | decimal(20,10) | YES  |     | NULL    |       |
| UTMHO       | decimal(20,10) | YES  |     | NULL    |       |
| BOX_GEO     | text           | YES  |     | NULL    |       |
| BOX_GK      | text           | YES  |     | NULL    |       |
| BOX_UTM     | text           | YES  |     | NULL    |       |
| is_pilsdorf | tinyint(4)     | YES  | MUL | NULL    |       |
+-------------+----------------+------+-----+---------+-------+

[Rolf B]

Hallo Raketenwilli,

ein Index hilft beim Order By nur dann, wenn Du die Ergebnismenge sehr signifikant einschränkst, so dass ein indexsequenzielles Lesen schneller ist als ein filternder Tablescan mit nachgelagertem Sort. Du liest die Table vollständig, da ist ein Index grundsätzlich nachteilig.

Und ein Tablescan ist grundsätzlich SEHR schnell, sofern der Tablespace ordentlich defragmentiert ist. Indexsequenzielles Lesen heißt: viele Kopfbewegungen, viel Zeit.

Rolf

[Raketenwilli]

Du liest die Table vollständig, da ist ein Index grundsätzlich nachteilig.

Alles klar: Mein Denkfehler. Danke, wieder was gelernt.

sofern der Tablespace ordentlich defragmentiert ist

Ich hab die Datenbank auf einer (SATA-III-)SSD. Ich nehme an, da ist das Defragmentieren nicht sooo notwendig - sondern auf Dauer sogar schädlich. Aber auch Tests mit einem 4TB-Massen-Magnetdatengrab haben ergeben, dass da zwar Dateien fragmentiert waren, aber da waren nur einzelne 4GB-Dateien in bis zu 16 Fragmenten… Angeblich macht ext4 das ganz gut.

ein Index hilft beim Order By nur dann, wenn Du die Ergebnismenge sehr signifikant einschränkst,