Abfrage mit order by desc langsam

[tausday]

Hallo,

ich habe schon versucht mittels Google mein "Problem" zu lösen jedoch ohne Erfolg:

Folgendes:
Wenn ich die Abfrage mit Order by ASC mache, dauert diese 0.0016 Sekunden.
Mache ich die gleiche Abfrage per DESC, dauert diese 0.0436 also knapp 30x so lange.

Woran kann das liegen?
29893 insgesamt, Die Abfrage dauerte 0.0016 Sekunden.

SELECT * FROM `cronjobrun` WHERE `client` LIKE ' PI_RASPI'AND on_off = 0 ORDER BY `cronjobrun`.`timestamp` ASC

29893 insgesamt, Die Abfrage dauerte 0.0436 Sekunden.

SELECT * FROM `cronjobrun` WHERE `client` LIKE ' PI_RASPI'AND on_off = 0 ORDER BY `cronjobrun`.`timestamp` DESC

Die Tabelle hat Rund 1,4Millionen eintraege und 6 Spalten. Die spalten "timestamp", "on_off" und "client" sind indexiert.

Grenze ich die suche noch weiter ein mit:

SELECT * FROM `cronjobrun` WHERE `client` LIKE ' PI_RASPI'AND on_off = 0 AND `timestamp` > "2015-11-27" ORDER BY `cronjobrun`.`timestamp` DESC

Dauert es: 71 insgesamt, Die Abfrage dauerte 0.0443 Sekunden.

Andersrum sortiert:

SELECT * FROM `cronjobrun` WHERE `client` LIKE ' PI_RASPI'AND on_off = 0 AND `timestamp` > "2015-11-27" ORDER BY `cronjobrun`.`timestamp` ASC

Dauert es:71 insgesamt, Die Abfrage dauerte 0.0102 Sekunden.

Sortiere ich die gleiche Abfrage nach der ID ist diese aufeinmal super schnell

SELECT * FROM `cronjobrun` WHERE `client` LIKE ' PI_RASPI'AND on_off = 0 AND `timestamp` > "2015-11-27" ORDER BY `cronjobrun`.`id` DESC

Dauert es:71 insgesamt, Die Abfrage dauerte 0.0007 Sekunden

Bei der letzten Abfrage bin ich am erstaunsten.
Obwohl bei den letzten drei Abfragen die Anzahl der Treffer immer gleich ist, ist die Abfrage mit der Sortierung über die ID mit DESC am schnellsten.

Sortiere ich die letzte Abfrage von DESC nach ASC um dauert es 0.0634 Sekunden lange.
Also rund 90x langsamer..... ich verstehe es einfach nicht.

Selbst wenn ich bei der letzten Zeile das Order by weg lasse dauert es noch 0.0559.. Also auch länger

Wie ist das möglich? Gibt es da eine Sinnvolle erklärung?

Vielen Dank

 

[Hubertus]

Beim CREATE INDEX kann man für die Spalten ASC oder DESC angeben. Das beeinflusst die Optimierung der Abfragen.

 

[akretschmer]

Bei solchen Fragen schaut man immer zuerst in das Explain.

 

[Hubertus]

Explain gibt's sogar bei MySQL.

 

[Hubertus]

Ein und dieselbe Abfrage, kurz hintereinander ausgeführt, läuft beim zweiten Mal deutlich schneller, egal mit welchem ORDER BY, weil die Daten vom ersten Mal noch im Hauptspeicher sind und nicht von der Platte gelesen werden müssen.

 

[akretschmer]

Ich sehe bei mir da keine großen Unterschiede:

test=# create table sort_table as select random() as val from generate_series(1,100000) s;
SELECT 100000
test=*# create index sort_index on sort_table(val);
CREATE INDEX
test=*# analyse sort_table;
ANALYZE
test=*# explain analyse select * from sort_table order by val limit 5;
  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..0.51 rows=5 width=8) (actual time=0.075..0.083 rows=5 loops=1)
  ->  Index Only Scan using sort_index on sort_table  (cost=0.29..4376.29 rows=100000 width=8) (actual time=0.073..0.080 rows=5 loops=1)
  Heap Fetches: 5
 Planning time: 7.142 ms
 Execution time: 0.117 ms
(5 rows)

test=*# explain analyse select * from sort_table order by val desc limit 5;
  QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.29..0.51 rows=5 width=8) (actual time=0.080..0.092 rows=5 loops=1)
  ->  Index Only Scan Backward using sort_index on sort_table  (cost=0.29..4376.29 rows=100000 width=8) (actual time=0.078..0.087 rows=5 loops=1)
  Heap Fetches: 5
 Planning time: 0.189 ms
 Execution time: 0.113 ms
(5 rows)

Wie gesagt, man schaut ins Explain. (bei mir ist es aber kein MySQL)

 

[tausday]

Hallo,

danke erstmal für die vielen Antworten bisher.
Ich habe erstmal query_cache_type auf OFF gesetzt, damit diese Daten nicht gepuffert werden.
Des weiteren habe ich die SQL Tabelle auf eine RAM-DISK verschoben, um zu sehen ob es am I/O der Festplatte liegt <----- Keine Verbesserung:

So nun zu dem Hinweis des "EXPLAIN"
Ich habe eben mal die SHELL gestartet, da man hier die Ergebnisse besser kopieren kann und komme irgendwie zu "anderen" Ergebnissen.

Also:

SELECT * FROM `cronjobrun` WHERE `client` LIKE ' PI_RASPI'AND on_off = 0 AND `timestamp` > "2015-11-27" ORDER BY `cronjobrun`.`id` DESC;

SHELL: 71 rows in set (0.06 sec)
PHPMYADMIN: Zeige Datensätze 0 - 24 (71 insgesamt, Die Abfrage dauerte 0.0007 Sekunden.) [id: 1424511... - 1401603...]

+------+-------------+------------+------+-------------------------+--------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+------+-------------------------+--------+---------+-------+-------+-------------+
| 1 | SIMPLE | cronjobrun | ref | on_off,client,timestamp | on_off | 4 | const | 62706 | Using where |
+------+-------------+------------+------+-------------------------+--------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

SELECT * FROM `cronjobrun` WHERE `client` LIKE ' PI_RASPI'AND on_off = 0 AND `timestamp` > "2015-11-27" ORDER BY `cronjobrun`.`id` ASC;

SHELL: 71 rows in set (0.06 sec)
PHPMYADMIN: Zeige Datensätze 0 - 24 (71 insgesamt, Die Abfrage dauerte 0.0577 Sekunden.) [id: 1376591... - 1382913...]

+------+-------------+------------+------+-------------------------+--------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+------------+------+-------------------------+--------+---------+-------+-------+-------------+
| 1 | SIMPLE | cronjobrun | ref | on_off,client,timestamp | on_off | 4 | const | 62706 | Using where |
+------+-------------+------------+------+-------------------------+--------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

Sieht für mich beides ähnlich aus.
Ich habe auch die Abfragen mehrmals hintereinander ausgeführt, um sicher zu stellen, das wirklich nichts gecached wird
Was mich nur wundert, ist bei der ersten Abfrage der zeitliche unterschied zwischen SHELL und PHPMYADMIN... lügt hier evtl. eine Anzeige -.-

ich habe die Datenbank einfach mal hochgeladen, vll. ist es dann ja einfach drüber zu sprechen.
LINK sind 8,5MB gezippt mit WINRAR (SQL Anweisung ist 100MB Groß)

Danke schonmal vorab

 

[akretschmer]

Es nutzt also nirgends einen Index. Der Zeitunterschied kommt, so vermute ich, weil der Sortieaufwand unterschiedlich ist. Die Daten liegen offenbar in der einen Richtung sortiert vor. Viel mehr kann man aus dem bescheidenen MySL-Explain kaum erraten.

 

[ukulele]

Man könnte noch eine neue Test-Tabelle befüllen und die Daten dort unterschiedlich "sortiert" rein schreiben. Das müsste dann mit beiden Sortierungen reproduzierbare Ergebnisse liefern.