# MySQL] Abfrage extrem langsam



## Moritz123 (6. August 2010)

Hallo,

ich habe hier eine Abfrage, die extrem lange braucht, obwohl sie nach meiner Ansicht gar nicht so komplex ist.
Kurz zur Statistik: Die Tabelle "auctions" hat ca 5 Millionen Einträge, die Tabelle auctions_ratings hat ca. 40Tsd Einträge, die Tabelle users hat ca. 15Tsd Einträge.
Was ich nun haben möchte, sind die Benutzer, die entweder ein bestimmtes Flag gesetzt haben oder bei denen der Wert des Feldes "rating" 100 entspricht. Als Ergebnis der Abfrage möchte ich die userID und die Anzahl der Ratings. Die Abfrage, um das zu realisieren sieht wie folgt aus:

```
SELECT u.*, count(r.id) as bw_count FROM `users` u
LEFT JOIN auctions_ratings r ON r.to_id = u.id 
WHERE 
(u.rating = 100 OR u.flag_trusted = 1)
GROUP BY u.id
ORDER BY bw_count DESC
```
Ausführungsdauer hier sind 0,09 sec - was absolut tragbar ist.
Nun kommt der kritische Punkt: Ich möchte nur die User haben, die einen Eintrag in der Tabelle auctions haben, wobei diese über das Feld auctions.user_id referenziert werden kann.
Ich habe nun zwei Varianten gestetet:

Variante 1)
Ich habe die Tabelle auctions über einen zusätzlichen LEFT JOIN angebunden, wobei mir das das Ergebnis von "bm_count" verfälscht. Diese Abfrage braucht ~140sec.

```
SELECT u.*, count(r.id) as bw_count FROM `users` u
LEFT JOIN auctions_ratings r ON r.to_id = u.id 
LEFT JOIN auctions a ON a.user_id = u.id 
WHERE 
(u.rating = 100 OR u.flag_trusted = 1)
AND 
a.date_end > NOW() AND a.root_category_id = 5
GROUP BY u.id
ORDER BY bw_count DESC
```

Variante 2)
Ich habe das Ganze mit einem Subselect umgesetzt:

```
SELECT u.*, count(r.id) as bw_count FROM `users` u
LEFT JOIN auctions_ratings r ON r.to_id = u.id 
WHERE 
(u.rating = 100 OR u.flag_trusted = 1)
AND 
u.id IN (SELECT a.user_id FROM auctions a WHERE 
a.date_end > NOW() AND a.root_category_id = 5) 
GROUP BY u.id
ORDER BY bw_count DESC
```
Wie lange diese Abfrage braucht weiß ich nicht, da ich nach ca.5 minuten abgebrochen habe.

Nun meine Frage: Seht ihr irgendwelche groben Schnitzer oder Performancekiller in der Abfrage? Wenn ja, gibt es Optimierungsmöglichkeiten?

Vielen Dank und schon einmal ein schöne Wochenende!


----------



## andy72 (7. August 2010)

Hi Moritz,

aus meiner beruflichen Erfahrung heraus haben sich für solche Datenmengen Relation-Tables in unserem Unternehmen durchgesetzt. Diese werden z.T. durch Cron-Jobs und/oder durch Seitenaufrufe gefüllt.

Evtl. kannst Du auch ein Caching System nutzen, daß Dir wahlweise die Query-Results vorhält oder komplette Code-Schnipsel die sich ständig wiederholen.

Auch wir hatten starke Performance-Probleme und sind denen auch nicht anders Herr geworden.
Beispiel: Du hast eine Community mit 500.000 Usern. Jeder schreibt etwas in ein Gästebuch eines anderen Users, oder will sich nur mal das Profil eines anderen Users anschauen, der dann auch noch angezeigt bekommt was der andere User denn so als letzte Aktivitäten hatte oder was er für virtuelle Freunde hat.
Da ist mit LEFT JOINS oder Subselects (die i.d.R. immer langsamer sind)  nicht mehr viel machbar, da die Abfragen einfach zu groß sind.

LG
Andy


----------



## Moritz123 (8. August 2010)

Hallo Andy!

Mhh ich habe sowas fast befürchtet. Dennoch würde ich mich interessieren, wie ihr das in den Griff bekommen habt? Meinst Du mit Relationstabellen, Tabellen, die sozusagen alle Daten der Relation enthalten? Würden hier evtl. Views einen Performancegewinn bringen?

Ich habe mich auch mal mit Literatur versorgt - ich hoffe ich erfahre da noch etwas mehr.

Besten Dank und einen schönen Sonntag!

Moritz


----------



## andy72 (8. August 2010)

Hi,

ja - genau solche Tabellen meine ich  Am besten Du baust diese Tabellen so auf, daß sie möglichst alle wichtigen Daten enthalten. Pass aber auf, daß sie nicht plötzlich dieselbe Datenmenge haben wie die Tables die Du vorher im JOIN hattest, ist dann auch wenig sinnvoll 

Hier nochmal ein Beispiel aus der Praxis:
Du hast zig Tausend User und willst in Deiner Ausgabe auf der Page immer nur die neuesten 5 User ausgeben. Sinnvoll ist hier eine eigene Tabelle die auch nur diese 5 Datensätze enthält - und das möglichst komplett. Das lässt sich zB gut über einen Cron regeln, der zB alle 15 Minuten die Table leert und dann 1x durch die Usertabelle geht und die Daten besorgt, so hat der Server nur alle 15 Min. mal kurz etwas mehr Last und dann nicht mehr


----------



## Moritz123 (8. August 2010)

Hi Andy,

ok - das mit dem Cron war ohnehin geplant. Nur werde ich da schon Probleme bekommen, da die Query sich scheinbar "tot"läuft und sie ca 8 mal ausführen muss, um die entsprechenden Daten für alle Root-Cats zu erzeugen.
Ich denke ich werde es auf zwei Querys aufteilen, die Applkation den Merge machen lassen und so den JOIN mit der bösen auctions-Tabelle umschiffen.

Habt ihr damals versucht mit VIEWs zu arbeiten? Mich würde interessieren, ob das ein nennenswertes Performanceplus bringt.

Vielen Dank und schöne Grüße,

Moritz


----------



## andy72 (8. August 2010)

Views hatten wir versucht - Angesichts der großen Datenmengen brachte das herzlich wenig, da IMHO MySQL die Daten auch nur in einer temporären Tabelle speichert und somit die Last nicht wesentlich weniger wurde. Ein Versuch war es aber Wert, denn Views nehmen wir inzw. für kleinere Abfragen die ebenfalls ständig wiederkehren und überschaubar sind.

Was ich Eingangs vergaß zu fragen: Nimmst Du InnoDB oder MyISAM Tables ? InnoDB ist auch etwas zügiger bei Arbeiten und kann auch mit großen Datenmengen besser umgehen habe ich festgestellt.

Was Du noch versuchen kannst: Arbeite mit Limit und Offset, wenn Du Daten selektierst - so ist die zu suchende Menge an Daten in der DB geringer - kannst ja mit PHP oder anderen Script-Sprachen abfragen ob noch Daten vorhanden sind nachdem der Query raus ist


----------



## Moritz123 (8. August 2010)

Hallo Andy.

Vielen Dank nochmal für die hilfreichen Ansätze. Ich werde morgen mal eine View erstellen, allerdings habe ich, nachdem was ich im Mysql Performance Blog gelesen habe, wenig Hoffnung, dass es etwas bringt.

StorageEngine ist InnoDB, da wir auf referenzielle Integrität angewiesen sind bzw. deren Vorzüge nutzen wollen.

Bezgl des Limits - bist Du dir da sicher, dass er nur auf Untermengen arbeitet? Wie macht er das dann bei ORDER? Werden nur die selektierten Untermengen sortiert?

Danke und Grüße,

Moritz


----------



## andy72 (8. August 2010)

Bezügl. des Limits mit Offset denke ich schon daß Du an gesuchte Datensätze schneller herankommst - Vorraussetzung ist natürlich dass Du einen PK hast den Du selektieren kannst - mit ORDER passiert im Grunde ja nichts weiter außer daß die DB die gefundenen Daten durchsortiert. Er kann ja nix sortieren was nicht ausgewählt wurde, somit findet dieser Vorgang ja erst nach dem SELECT statt. Ich würde mir also darüber nicht den Kopf zerbrechen 

LG
Andy


----------

