Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Abfrage mit order by desc langsam

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von tausday, 28 November 2015.

  1. tausday

    tausday Neuer Benutzer

    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.
    Code:
    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.
    Code:
    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:
    Code:
    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:
    Code:
    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
    Code:
    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
     
  2. Hubertus

    Hubertus Fleissiger Benutzer

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

    akretschmer Datenbank-Guru

    Bei solchen Fragen schaut man immer zuerst in das Explain.
     
    Hubertus gefällt das.
  4. Hubertus

    Hubertus Fleissiger Benutzer

    Explain gibt's sogar bei MySQL.
     
    akretschmer gefällt das.
  5. Hubertus

    Hubertus Fleissiger Benutzer

    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.
     
  6. akretschmer

    akretschmer Datenbank-Guru

    Ich sehe bei mir da keine großen Unterschiede:

    Code:
    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)
     
    Hubertus gefällt das.
  7. tausday

    tausday Neuer Benutzer

    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:
    Code:
    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)

    Code:
    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
     
    Hubertus gefällt das.
  8. akretschmer

    akretschmer Datenbank-Guru

    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.
     
    Hubertus gefällt das.
  9. ukulele

    ukulele Datenbank-Guru

    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.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden