MySQL tmp Tables problem

[sterni]

Guten Morgen

Mein Hoster hatte mich angeschrieben, ich soll meine Queries Optimieren und komme nicht weiter. Ich hoffe ihr könnt mir helfen.

Text aus der Mail

Ihr Server ist mit etwa 200MB großen MySQL tmp Tables regelmäßig Verursacher von kurzen I/O Peaks, die auch den await in die Höhe treiben.

Folgende Arten von Queries sind mir hierbei aufgefallen:
SELECT *,id as spielid, categoryid as gcat,
(select count(id) FROM `jos_community_games_fav` WHERE gid = spielid) as favorit,
(select voteavg FROM `jos_community_games_votes` WHERE id = spielid) as durchschnitt,
(select uname FROM `jos_community_games_scores` WHERE Rang = 1 and gid = spielid) as topscorerid,
(select username FROM jos_users WHERE id = (select uname FROM `jos_community_games_scores` WHERE Rang = 1 and gid = spielid)) as topscorername,
(select title FROM `jos_community_games_category` WHERE id=gcat) as gamecat FROM `jos_community_games`
WHERE `published`='1' AND `scoring_structure` != '0' ORDER BY rand() ASC LIMIT 0, 12

Wie kann ich diese Abfragen Optimieren...?
Kann man die tmp Tables löschen bzw. leeren?
Was sind den I/O Peaks?

[ParkingClinic]

Abfrage optimieren:
Gedanken über die Daten und dessen Strukturierung machen
Weiterführende Gedanken darüber welche Daten wie benötigt werden
Dahingehend Datenbankstruktur anlegen Indexe setzen und Querys schreiben

Prinzipiell nicht so schwierig, muss man sich nur mit beschäftigen.

Die tmp-Tables werden während der Ausführung des Querys angelegt, eine art Zwischenablage, wird danach wieder vernichtet - brauchst du also nicht löschen ;)

IO ist Input/Output ; die tmp-Tables werden nach Möglichkeit im Ram gehalten, wird der benötigte Platz zu groß werden diese auf die Festplatte ausgelagert, welches um ein vielfaches langsamer ist.

Die Datenmenge die nun auf den Bereich geschrieben und auch wieder gelesen wird ist bei einem PEAK deutlich höher bzw. meißt der höchste Stand des üblichen normalbetriebes.

[jpwfour]

Wow, ein Select mit 5 subselects, und am ende dann noch order by rand, womit zuerst alle gesammelten daten in einer temp. tabelle abgelegt werden müssen, um am ende dann zufällig 12 rauszusuchen.

Was ist "jos_community_games" denn?

Man müsste wissen, was genau damit bezweckt werden soll.
Wenn es um die Auflistung 12 zufälliger Einträge aus einer Tabelle geht, zu denen man dann noch weitere Zusatzdaten benätigt, ist hier evtl die Aufteilung in mehrere Anfragen sinnvoll, also zuerst aus 1er Tabelle via rand() 12 Einträge holen, dann erst in weiteren Anfragen die zusätzlichen Daten.

Scheinbar wird aber auch jedesmal über ein COUNT() gezählt, wie viele User diesen Eintrag als Favorit haben, das sollte man auch vermeiden und besser einen eigenen Counter dafür führen, der halt jedesmal +-1 gemacht werden muss, wenn ein User was an seinen Favoriten ändert. uvm...

Siehe dazu auch:
http://dev.mysql.com/doc/refman/5.1/de/explain.html

Wenn man dem ganzen ein EXPLAIN voranstellt, sagt einem MySQL, was intern genau gemacht wird, daran sieht man dann schnell, wo kein Index genutzt wird usw.

[sterni]

Danke für eure hilfe hat mir sehr geholfen :)