MySQL LIMIT Performance

[Moloc]

Hallo,
(siehe Post 3 - es ist doch kein HAVING problem)
ich importiere täglich ca. 30.000 Datensätze in meine lokale Datenbank, um daraus Statistiken zu erstellen. Da kommt es hin und wieder vor, dass einige Datensätze doppelt importiert werden (aber das ist ein anderes Problem). Nun, ob nun doppelte Datensätze vorhanden sind, überprüfe ich mit folgendem SQL:

SELECT * FROM `points`
WHERE DATE(points_time) = CURRENT_DATE()
GROUP BY points_town_id
HAVING COUNT(*) > 1

EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE points index NULL points_town_id 4 NULL 1177265 Using where

Mir ist nun aufgefallen, dass jeder Tag das SQL länger benötigt, um mir eine Antwort zu geben. Derzeit dauert das Script 106 Sekunden (Vor einigen Tagen lief es noch in 60 Sekunden). Lasse ich jedoch das GROUP BY und das HAVING weg, dauert es nur 2 Sekunden.

Wenn ich das SQL folgend abändere,

SELECT *
FROM (
  SELECT * FROM `points`
  WHERE DATE(points_time) = CURRENT_DATE()
) as x
GROUP BY points_town_id
HAVING COUNT(*) > 1

EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 31772 Using temporary; Using filesort
2 DERIVED points ALL NULL NULL NULL NULL 1177265 Using where

läuft dies in 2 Sekunden durch, mit dem selben Ergebnis.

Ich habe einmal gelernt, dass bei SQL erst WHERE abgearbeitet wird, und dann erst GROUP BY etc.. Optimiert hier SQL in die falsche Richtung? Kann sich das jemand erklären? Eigentlich sollten die beiden SQL Statements doch gleich schnell sein?

Moloc

 

[Sebbo]

Hallo,

ich würde vermuten, dass das zweite SQL geringfügig länger brauchen sollte als das erste. Der Grund, dass es so schnell ist, könnte der MySQL Query cache sein. (D.h. das Ergebnis der ersten Abfrage wird gecacht (wahrscheinlich nur der Where-Teil) und bei deinem zweiten Query einfach wieder genutzt. Damit geht das viel schneller. Bitte versuch mal, mit einem

RESET QUERY CACHE

den Cache zu löschen und führe dann das zweite Query noch mal aus.

Und noch ein Hinweis: Ein "Select *" kombiniert mit "GROUP BY" macht keinen Sinn (und ist in anderen SQL-Varianten als MySQL auch gar nicht möglich). Ich würde schätzen du suchst was wie:

SELECT count(id), points_town_id FROM `points`
WHERE DATE(points_time) = CURRENT_DATE()
GROUP BY points_town_id
HAVING COUNT(*) > 1

Was die Abfrage langsam macht, dürfte dein "DATE(points_time)" sein. Wenn das ein Timestamp ist, dann versuche doch, das ganze auch als Timestamp zu handeln und nicht erst in ein Datum umzuwandeln (denn das muss MySQL für jede Zeile machen und das dauert). Evtl. dann auf points_time noch nen Index setzen.

 

[Bububoomt]

Und noch ein Hinweis: Ein "Select *" kombiniert mit "GROUP BY" macht keinen Sinn (und ist in anderen SQL-Varianten als MySQL auch gar nicht möglich). Ich würde schätzen du suchst was wie:

In 99,9999999% der Fälle gebe ich dir recht, habe aber schon Fälle gehabt wo es Sinn macht und wo ich mich dann sehr geärgert habe das es by MSSQL nicht ging und ich was kompliziertes konstruieren musste.

 

[Moloc]

Danke für die Antwort.

Ich habe nun eine eigene Spalte für das Datum erstellt und darauf ein index gelegt (ich hatte das zuvor schon einmal probiert, aber damals sah ich keine Performance-Verbesserungen und habe es wieder verworfen).

Das SELECT * hab ich nun auch abgeändert. Das war mir bewusst, dass das nicht korrektes SQL ist.
Aktueller Query:

SELECT points_town_id
FROM points
WHERE points_date = CURRENT_DATE()
GROUP BY points_town_id
HAVING COUNT(points_town_id) > 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE points ref points_date points_date 3 const 64936 Using where; Using temporary; Using filesort

Beide queries laufen nun in 0.1 Sekunden.

Wie ich nun herausgefunden habe, scheint es am LIMIT zu liegen. Ich habe die Queries immer in phpMyAdmin ausprobiert, und dieses fügt dem Query automatisch ein LIMIT 0, 30 hinzu.

Da ich nicht genau weiß, was phpMyAdmin nun im Hintergrund noch alles macht, habe ich das query selbst mit php abgesetzt.
Das zweite Query braucht immer gleich lang. Beim ersten Query mit LIMIT gibt es jedoch gewaltige unterschiede.

LIMIT 0, 30 ~126 Sekunden
LIMIT 0, 50 ~0.1 Sekunden
LIMIT 0, 100 ~0.1 Sekunden
LIMIT 1, 30 ~126 Sekunden
LIMIT 20, 10 ~126 Sekunden
LIMIT 30, 30 ~0.1 Sekunden
LIMIT 100, 30 ~0.1 Sekunden
LIMIT 1000, 100 ~0.1 Sekunden

Das sieht jedenfalls nicht normal aus.

Dann muss ich nun am LIMIT drehen :/

Danke.
Moloc

 

[ice-breaker]

Das deine Werte nicht stimmen können, ist dir aber selbst schon klar oder?
Also die ersten 3 Werte sind einfach absolut unmöglich.

Tipp: Der erste Query braucht so lange weil die Daten in Caches geladen werden (MySQL-Cache, Betriebssystem, HDD), danach ist es für die folgenden Queries natürlich schneller. Deine Feststellung, dass der erste Query große Unterschiede hat, zeigt ja schon, dass in deinem "Benchmark" etwas nicht stimmt.
Wenn du hier nach Tipps fragst, solltest du aber schon mehr Infos anbringen, dazu gehört z.B. das Tabellen-Schema, die Anzahl der Einträge, Datensätze die durch deine Selektion ausgewählt werden, gebildete Gruppen usw.

Fazit: Du hast noch immer nicht das eigentliche Problem gefunden sondern stocherst nur auf gut Glück herum das Problem zu finden.

 

[transversalis]

Ohne den Tabellenaufbau und die Statistiken zu kennen gehe ich davon aus, dass sich ein Index über
BEIDE Spalten ( points_date , points_town_id ) positiv auf die Performance auswirken wird.

Die LIMIT x,y Angabe kann übrigens durchaus Einfluß auf den Zugriffspfad haben und die angegebenen Werte sind durchaus konsistent.

 

[Moloc]

Das deine Werte nicht stimmen können, ist dir aber selbst schon klar oder?

Wenn ich es nicht selbst testen und hier schreiben würde, würde ich das eben auch nicht glauben.

Also, dann versuche ich es nochmals erneut mit mehr Informationen:
Auf meinem Laptop läuft Win7. Darauf läuft Virtualbox mit Windows XP als Gastsystem. Auf dem Gastsystem ist XAMP installiert. Soweit zum System.
Die Datenbankinfos sind folgende:

--
-- Tabellenstruktur für Tabelle `points`
--

CREATE TABLE IF NOT EXISTS `points` (
  `points_town_id` int(11) NOT NULL,
  `points_player_id` int(11) NOT NULL,
  `points_point` int(11) NOT NULL,
  `points_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `points_date` date NOT NULL,
  KEY `points_player_id` (`points_player_id`),
  KEY `points_town_id` (`points_town_id`),
  KEY `points_date` (`points_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Für jeden Tag (points_date) gibt es eine eindeutige Liste von Städten (points_town_id), die einen Spieler (points_player_id) und Punkteanzahl (points_point) besitzen. points_time ist der genaue import-zeitpunkt.

Tabelle Einträge Typ Kollation Größe Überhang
points ~1,208,909 InnoDB utf8_general_ci 136,3 MiB -

Wie gesagt, möchte ich nun feststellen, ob beim Import etwas schief gegangen ist und ein Eintrag doppelt vorhanden ist (ok, wenn ich nun darüber nachdenke, müsste man dies mit einem UNIQUE auf points_date und points_town_id lösen können). Dies frage ich mit folgendem SQL ab (bzw. ich habe es bisher in phpMyAdmin eingetippt):

SELECT points_town_id
FROM points
WHERE points_date = CURRENT_DATE()
GROUP BY points_town_id
HAVING COUNT(points_town_id) > 1

phpMyAdmin hängt da ein LIMIT 0, 30 an. Dadurch, dass dieses Query über 100 Sekunden dauert und ich durch ähnliche Querys (siehe Ursprungspost) schnellere Laufzeiten herbekommen habe, habe ich hier nachgefragt, wieso das sein kann. Im zweiten Post habe ich dann den Query etwas optimiert, wodurch die lange Laufzeit immer noch lange blieb, und die kurze Laufzeit noch kürzer wurde. Soweit sogut.

Nach mehreren Tests bin ich dann draufgekommen, dass die Querys ohne "LIMIT 0, 30" innerhalb von 0.1 Sekunden abgearbeitet wurden. Was mich jedoch etwas stutzig macht, ist jenes, dass bei bestimmten LIMIT-Werten (siehe Tabelle von Post #4) die Laufzeit > 100 Sekunden ist. Mit den genannten LIMIT-Werten erhalte ich immer diese langen/kurzen Laufzeiten (auch wenn ich denselben Query 5-mal hintereinander abrufe).
Genau das ist es, was mich wundert.

[edit]
Ich habe nun ein UNIQUE auf das points_date mit points_town_id gelegt und wie es scheint, hattet ihr recht Nun laufen alle Querys unter 0.03 Sekunden.

Vielen Dank!
[/edit]

Moloc

 

[transversalis]

Egal, ob Du LIMIT 0,30 oder LIMIT 10,20 oder LIMIT 20,10 schreibst, das Datenbanksystem muss immer die ersten 30 Sätze ermitteln.
Bei LIMIT 0,50 oder LIMIT 0,100 ist sind es immer 50+ Sätze.

Es ist möglich, dass MySQL aufgrund der ihm bekannten Statistiken, Systemresorcen usw. für "hole mir die ersten dreissig Sätze" einen anderen Zugriffspfad wählt, als für "hole mir die ersten 100 Sätze".

Wenn Du der Reihe nach vorgibst
LIMIT 0,30
LIMIT 0,31
LIMIT 0,32
...
wirst Du feststellen, dass bei Erreichen eines bestimmten Schwellwertes sich die Antwortzeit schlagartig ändert. Und dies reproduzierbar.


[edit]
wenn Du tatsächlich einen UNIQUE Index über die beiden Spalten gelegt hast ( und demnach jede Kombination nur einmal vorkommen kann ), dann dürfte die Query allerdings keinen einzigen Satz als Ergebnis zurückliefern
[/edit]

 

[Moloc]

Egal, ob Du LIMIT 0,30 oder LIMIT 10,20 oder LIMIT 20,10 schreibst, das Datenbanksystem muss immer die ersten 30 Sätze ermitteln.

Sowas ähnliches habe ich mir gedacht, deshalb habe ich die Testfälle dementsprechend auch ausprobiert (ja, soviele sind es nun nicht )

wenn Du tatsächlich einen UNIQUE Index über die beiden Spalten gelegt hast ( und demnach jede Kombination nur einmal vorkommen kann ), dann dürfte die Query allerdings keinen einzigen Satz als Ergebnis zurückliefern

Das stimmt natürlich auch. Somit ist mein Query überflüssig.

Danke nochmals für die Infos und Hilfe.

Moloc

 

[ice-breaker]

Die LIMIT x,y Angabe kann übrigens durchaus Einfluß auf den Zugriffspfad haben und die angegebenen Werte sind durchaus konsistent.

Das natürlich, aber ich habe mich in meiner Aussage auch strikt auf die ersten 3 WErte begrenzt. Und hier handelte es sich um ein "LIMIT 0,30", "LIMIT 0,50" und ein "LIMIT 0,100". Da alle in der Ausführung identisch sind, dass die Ergebnissmenge beim Finden eines Ziels begrenzt wird (und keine vorher übersprungen werden), ist es eben höchst unwahrscheinlich, dass der Query mit nur 30 Ergebnisse länger als mit 50 oder 100 Ergebnissen dauert. Der Ausführungspfad wird identisch sein und einzig die Abbruchbedingungen der Queries sind verschieden, wobei ersterer früher aufhören kann als die anderen Queries und somit eine geringere Laufzeit aufweisen sollte.

Ohne den Tabellenaufbau und die Statistiken zu kennen gehe ich davon aus, dass sich ein Index über
BEIDE Spalten ( points_date , points_town_id ) positiv auf die Performance auswirken wird.

Die LIMIT x,y Angabe kann übrigens durchaus Einfluß auf den Zugriffspfad haben und die angegebenen Werte sind durchaus konsistent.

Das stimmt natürlich auch. Somit ist mein Query überflüssig.

würdest du mich bitte trotzdem an einem Explain des Querys teilhaben lassen? Mich würde in diesem Falle nämlich interessieren ob MySQL schon vor der Ausführung erkennt, dass der Query unerfüllbar ist oder ob es ihn stupide ausführt und einfach kein Ergebnis zurückliefert.
Teilweise erkennt MySQL solche unmöglichen Fälle nämlich (WHERE a = 1 AND a = 2). Ob dies auch bei einem komplexeren Query wie diesem funktioniert interessiert mich wirklich brennend.

 

[Moloc]

würdest du mich bitte trotzdem an einem Explain des Querys teilhaben lassen?

Ja, kein Problem:

  UNIQUE KEY `points_town_by_date` (`points_date`,`points_town_id`),

EXPLAIN
SELECT points_town_id
FROM points
WHERE points_date = CURRENT_DATE()
GROUP BY points_town_id
HAVING COUNT(points_town_id) > 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE points ref points_town_by_date,points_date points_town_by_date 3 const 64896 Using where; Using index

Ich hab deine Anmerkung zu doppelten WHERE Bedingungen auch noch ausprobiert:

EXPLAIN
SELECT points_town_id
FROM points
WHERE points_date = CURRENT_DATE() and points_date = "2002-04-05"
GROUP BY points_town_id
HAVING COUNT(points_town_id) > 1

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE

 

[ice-breaker]

Vielen Dank, dann erkennt MySQL die Unmöglichkeit einer Bedingung scheinbar nur bei simplen Fällen.