[MySQL] Query performance met datetime

[Astromenia]

Ik heb een database waarin ik elk uur data log. Nu wil ik uit die database de eerste rij van vandaag hebben. Ik doe dat met de volgende query:
SELECT MIN(units) as units FROM data WHERE meter_id = 11 AND DATE(timeStamp) = CURDATE() limit 1;

Ik heb dus een tabel data met een kolom units, meter_id en een timestamp(datetime). Op de kolom meter_id heb ik een index.

Mijn probleem is nu dat de performance mij erg tegen valt. Bovenstaande query komt binnen 0.11 sec terug uit een tabel met 37000 records waarbij er twee verschillende meter_id's zijn. Volgens mij zou mysql dus moeten zoeken over ongeveer de helft van 37000. Verwijder ik de index van meter_id dan komt de query terug in 0.025 sec. Ook het laatste vind ik nog vrij lang.

Waar doe ik het fout?

[Creepy]

Ik denk niet dat je wat fout doet. Het is nogal afhankelijk van de tabel grootte (filesize dus), MySQL instellingen, of de server druk belast is etc. etc.

Dus kijk eens wat de server aan het doen is. Kijk vervolgens eens naar de MySQL instellingen of daar nog wast te tunen valt. Je kan ook een samengestelde index gebruiken om meter_id en timeStamp maar dan zul je je query wat om moeten bouwen zodat je de DATE(timeStamp) contructie niet meer nodig hebt.

[smesjz]

Je kan ook nog eens een 'EXPLAIN' er op los laten om te zien wat ie precies met die meter_id index doet.

[Voutloos]

Een index op iets met een cardinaliteit van 2 heeft vaak geen nut. Mysql zal dan bijvoorbeeld bepalen dat je al dusdanig veel rows moet gebruiken dat een table scan efficienter is.

Ik zou inderdaad eerst een samengestelde index proberen. Dat zal sowieso een stuk efficienter zijn, want je where filter al lekker weg dan.

Overigens zeg je 'de eerste' maar met MIN() en LIMIT 1 tegelijk doe je dat niet.

edit:
O wacht, de aanname dat de 1e meterstand het laagste is...

[ Voor 8% gewijzigd door Voutloos op 10-03-2010 13:48 ]

[Joolee]

Ik denk dat je de performance enorm kunt verbeteren door van tevoren de timestamp om te zetten naar een date veld. Daarmee kan MySQL veel sneller werken dan een timestamp om te moeten zetten naar het date formaat.

In deze situatie moet MySQL dus zo'n 18500 timestamps om gaan zetten naar het date formaat bij iedere query.

[BramT]

Wilde net toevoegen wat Jilde zei. De DATE() aanroep is hier geloof ik zelfs helemaal overbodig. Een TIMESTAMP kan je 'native' al vergelijken met CURDATE(); het lijkt me niet eens nodig om de kolomtype om te bouwen.

[Astromenia]

Creepy schreef op woensdag 10 maart 2010 @ 13:35:
Je kan ook een samengestelde index gebruiken om meter_id en timeStamp maar dan zul je je query wat om moeten bouwen zodat je de DATE(timeStamp) contructie niet meer nodig hebt.

Hoe dien ik mijn query dan om te bouwen?

[Kalentum]

Die DATE() functie is de bottleneck.

Als je geen datum-kolom hebt of wilt hebben voor dat veld kun je ook iets doen als:

code:

timeStamp >= '2008-07-31 00:00:00' AND timeStamp <= '2008-07-31 23:59:59'

[Kalentum]

BramT schreef op woensdag 10 maart 2010 @ 13:55:
Wilde net toevoegen wat Jilde zei. De DATE() aanroep is hier geloof ik zelfs helemaal overbodig. Een TIMESTAMP kan je 'native' al vergelijken met CURDATE(); het lijkt me niet eens nodig om de kolomtype om te bouwen.

Is dat zo? Ik heb een tabel met een veld created (timestamp):

code:

+---------------------+
| created             |
+---------------------+
| 2010-03-10 14:04:23 |
| 2010-03-10 14:04:14 |
| 2010-03-10 14:04:04 |
| 2010-03-10 14:03:26 |
| 2010-03-10 14:03:26 |
| 2010-03-10 14:03:26 |
| 2010-03-10 14:03:25 |
| 2010-03-10 14:03:25 |
| 2010-03-10 14:03:25 |
| 2010-03-10 14:03:25 |
+---------------------+

Maar als ik doe 'SELECT created FROM history WHERE created = CURDATE();' krijg ik geen resultaten.
Mysql 5.1.41

[Astromenia]

BramT schreef op woensdag 10 maart 2010 @ 13:55:
Wilde net toevoegen wat Jilde zei. De DATE() aanroep is hier geloof ik zelfs helemaal overbodig. Een TIMESTAMP kan je 'native' al vergelijken met CURDATE(); het lijkt me niet eens nodig om de kolomtype om te bouwen.

Ik heb dit ook even getest en dat werkt idd niet

[Astromenia]

rutgerw schreef op woensdag 10 maart 2010 @ 14:01:
Die DATE() functie is de bottleneck.

Als je geen datum-kolom hebt of wilt hebben voor dat veld kun je ook iets doen als:

code:

timeStamp >= '2008-07-31 00:00:00' AND timeStamp <= '2008-07-31 23:59:59'

Dit helpt behoorlijk! Ook de index op meter_id EN timestamp is een goede keuze. De data komt nu binnen 0.0008 sec terug! Explain geeft de volgende output:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE water range meter_id meter_id 12 NULL 23 Using where

De 'server' is overigens een Intel Atom N330 welke de rest van de tijd uit zijn neus staat te eten.

[ Voor 7% gewijzigd door Astromenia op 10-03-2010 14:20 ]

[Hydra]

rutgerw schreef op woensdag 10 maart 2010 @ 14:06:
Maar als ik doe 'SELECT created FROM history WHERE created = CURDATE();' krijg ik geen resultaten.

Logisch aangezien het een datetime is en die natuurlijk, tenzij je binnen de precisie (millisecond?) de insert en de select doet, nooit exact gelijk is. Het zou op te lossen zijn door gewoon de date op te slaan en die als index te gebruiken.