[mysql] Query optimaliseren werkt niet (filesort)

[Verwijderd]

Ik heb een hele grote mysql tabel met 456,927 records, de hele tabel is 454.1 MB.

Nu heb ik een leuke sql query:
select l.*
FROM logs2 l
WHERE l.siteid='1051' and l.dontshow=0 and l.datum >= '1172448000' and l.datum < '1173052800'
ORDER BY l.datum desc
limit 30

Deze query word redelijk vaak uitgevoerd en het is daarom gewenst dat hij wat efficienter word. Ik heb me verdiept in optimalisatie in mysql en de EXPLAIN commando gevonden.

Als ik een explain doe dan krijg ik het volgende:

code:

 id      select_type     table       type    possible_keys       key     key_len     ref     rows    Extra
1   SIMPLE  l   ref     datum,siteid,dontshow   siteid  4   const   690     Using where; Using filesort

In de laatste kolom staat dat hij "filesort" gebruikt, dat houd simpel gezegd in dat mysql de harddisk gebruikt en niet het veel snellere geheugen.

Het probleem is nu dat ik die filesort alleen weg kan krijgen als ik "ORDER BY l.datum desc" uit de query haal. Maar dat is helaas niet mogelijk omdat ik dan de verkeerde data terug krijg.

Ik heb indexes gedefinieerd op:

Keyname Type Cardinality
PRIMARY PRIMARY 410075
datum INDEX 410075
siteid INDEX 10514
moodid INDEX 281
dontshow INDEX 2

Kan iemand mij raad geven?

[Knutselsmurf]

Allereerst moet het aantal records wat je terugkrijgt natuurlijk niet afhankelijk zijn van een ORDER BY. Verder is het zo, dat MySQL slechts 1 index per tabel kan gebruiken. Je zult in dit geval dus een samengestelde index moeten maken. Daarbij is de volgorde van de velden in de index van belang. Met een Index (A,B) kun je zoeken op alleen veld A, en op de combinatie (A,B). Alleen zoeken op veld B kan dus niet.

[Voutloos]

Nofi, maar een index op (siteid, datum) proberen lijkt me nogal voor de hand liggen?
De index siteid wordt nu gebruikt, en je wil ordenen (en filteren) op datum. siteid en datum dus.

Lees wat over explain en optimalisatie. Zie de optimalisatie sectie in de mysql doc, en blogs als http://www.mysqlperformanceblog.com/

Knutselsmurf schreef op maandag 28 mei 2007 @ 23:03:
Allereerst moet het aantal records wat je terugkrijgt natuurlijk niet afhankelijk zijn van een ORDER BY

Het aantal zal wel goed zijn, maar het zijn niet de 30 meest recente entries en daarom dus verkeerd.

[ Voor 32% gewijzigd door Voutloos op 29-05-2007 10:36 ]

[Verwijderd]

Wow, dank je mensen, ik wist niet dat er een gecombineerde index in mysql mogelijk was. Ik zat al te denken waarom in hemelsnaam mysql de indexes niet gebruikte, maar dat komt dus omdat mysql niet meerdere indexes kan gebruiken (alleen een gecombineerde dus).

Nu ik een gecombineerde index heb gemaakt gebruikt mysql geen filesort meer. Ik moet nog kijken of dat in de praktijk wat uitmaakt met betrekking to snelheidswinst, maar ik neem aan van wel.

[Voutloos]

Ja, dat maakt uit. Zeker met een log tabel waarvan je de zekerheid hebt dat hij gestaag groeit.