[MySQL/PHP] Eerste resultaat per maand in jaar

[Astromenia]

Hallo allemaal,

ik heb een tabel met meetdata (entiteit_id, timeStamp, waarde, index op entiteit_id, timeStamp) waaruit ik over een jaar graag de eerste resultaten van de maand wil hebben. Nu ben ik zelf tot de volgende query gekomen:

SQL:

SELECT entiteit_id, waarde, TIMESTAMP
FROM  `water` 
WHERE TIMESTAMP
BETWEEN  '2010-06-01'
AND  '2011-06-01'
GROUP BY YEAR( TIMESTAMP ) , MONTH( TIMESTAMP ) , entiteit_id

Het resultaat komt na ongeveer 2,5 seconde terug bij 10 entiteiten die elk eens per uur een punt hebben geinsert in dat jaar en ziet er als volgt uit:

code:

9011559";"18509";"2010-06-01 00:00:17"
"9011560";"13378";"2010-06-01 00:00:23"
"9011561";"15341";"2010-06-01 00:00:40"
"9011565";"21334";"2010-06-01 00:00:48"
"9011567";"20474";"2010-06-01 00:00:06"
"9011568";"42237";"2010-06-01 00:00:36"
"9011569";"3184";"2010-06-01 00:00:33"
"9011570";"65660";"2010-06-01 00:00:59"
"9011571";"22180";"2010-06-01 00:00:16"
"9011573";"22049";"2010-06-01 00:00:58"
"9011574";"16068";"2010-06-01 00:00:16"
"9011575";"127";"2010-06-01 00:01:00"
"9011576";"18978";"2010-06-01 00:00:46"
"9011559";"27088";"2010-07-01 00:00:16"
"9011560";"23063";"2010-07-01 00:00:09"
"9011561";"24831";"2010-07-01 00:00:57"
"9011565";"27465";"2010-07-01 00:00:06"
"9011567";"29992";"2010-07-01 00:00:08"
"9011568";"48911";"2010-07-01 00:00:55"
"9011569";"3283";"2010-07-01 00:00:46"
"9011570";"78376";"2010-07-01 00:00:39"
"9011571";"38577";"2010-07-09 10:15:05"
"9011572";"41703";"2010-07-31 11:45:04"
"9011573";"28496";"2010-07-01 00:00:08"
"9011574";"25006";"2010-07-01 00:00:06"
"9011575";"160";"2010-07-01 00:00:21"
"9011576";"26073";"2010-07-01 00:02:30"
"9011559";"35293";"2010-08-01 00:00:08"
"9011560";"27467";"2010-08-01 00:00:41"

... ETC
Dit gaat nog een eindje door maar het idee zal duidelijk zijn. Heeft er iemand enig idee hoe dit sneller kan. Zelf denk ik dat ik om die YEAR en MONTH functie heen zal moeten.

code:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  water   ALL timeStamp   NULL    NULL    NULL    557186  Using where; Using temporary; Using filesort

Alvast bedankt voor de hulp!

[Voor 1% gewijzigd door RobIII op 05-07-2011 20:38. Reden: Code tags toegevoegd]

[RobIII]

Kijk eens naar having en min() etc. en lees sowieso even Hoe werkt dat GROUP BY nu eigenlijk? door want je Group by is niet goed atm.

Als het je om "sneller" te doen is moet je eens gaan kijken naar indexen; die zie ik nu niet gebruikt worden.

Al met al nogal wat zaken/punten om te verbeteren

Tot slot: Als je code, SQL queries etc. post, gebruik dan code tags a.u.b. Dat houdt je topic een stuk leesbaarder.

[Voor 140% gewijzigd door RobIII op 05-07-2011 20:38]

[Astromenia]

Na een vakantie inmiddels weer bezig ;-)

Ik heb momenteel de volgende query:

code:

SELECT entiteit_id, waarde, timeStamp, date_format(timeStamp, '%y-%m')
FROM `water` WHERE TIMESTAMP BETWEEN '2010-06-01' AND '2011-06-01' 
GROUP BY entiteit_id, date_format(timeStamp, '%y-%m')

Hiermee vraag ik iig in de select dezelfde waarden als waarop ik group. Er zit nu een index op de timeStamp die ook wordt gebruikt. De query geeft nu resultaat in ongeveer 1.8 seconde. Een verbetering maar het kan vast sneller. Ik zie nog niet in hoe min() en having mij kan helpen. Kun je hier wat over uitweiden?

[Voutloos]

Het is nu puur toeval dat je het eerste record van elke maand ziet, want dat specificeer je nergens in je query. Voer eens in gedachten je eigen query uit en bepaal voor jezelf hoe je de waarde voor de kolom waarde bepaalt.

Extra hint: Denk hier uitvoerig over na en lees nogmaals de links van Rob. Lees vervolgens nog een uurtje wat over de nieuw ontdekte termen en ontdek dan dat de volgende term het antwoord gaat zijn op je vraag: groupwise maximum.

Kleine lettertjes: Als je meteen het laatste punt uit de hint gaat zoeken zal je er de ballen van snappen. Enkel als je alle stappen volgt kan je een volleerd guru worden. Wax on, wax off, wax on wax off.

[Voor 7% gewijzigd door Voutloos op 26-07-2011 22:57]

[Astromenia]

Ik ben weer een stap verder, dank voor jullie commentaar!

Ik heb nu de volgende query:

code:

SELECT p.entiteit_id, p.waarde, p.timeStamp
FROM water AS p
JOIN (
SELECT entiteit_id, MIN( timeStamp ) AS timeStamp FROM water where timeStamp
BETWEEN  '2010-06-01'
AND  '2011-06-01'
GROUP BY entiteit_id, date_format( timeStamp, '%y-%m' )) AS pm 
ON p.entiteit_id = pm.entiteit_id
AND p.timeStamp = pm.timeStamp

Bovenstaande garandeerd dat ik steeds de eerste in de maand heb. De query komt nu binnen 1.6 seconde terug. Hieronder de explain: ( entiteit = meter, waarde = units)

code:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    121  
1   PRIMARY p   ref meter_id,timeStamp,meter_id_2   meter_id_2  42  pm.meter_id,pm.timeStamp    1   Using index
2   DERIVED water   index   timeStamp   meter_id    42  NULL    528890  Using where; Using index; Using temporary; Using f...

Ik ben benieuwd of jullie denken dat dit nog sneller zou kunnen. Momenteel heb ik namelijk 21 entiteiten maar dit kunnen ook goed 100 of 200 worden.