Snelheid van queries
Ik heb een database met 8 verschillende variabelen. Van deze variabelen wil ik:
- Dag max/min
- Maand max/min
- jaar max/min
- Gister max/min
En bijbehorende datum en tijd.
Ik gebruik hiervoor de volgende query:
SELECT changed_on, $SensorNamen[$i] FROM sensorwaardes WHERE YEAR(`changed_on`)=$Year AND MONTH(`changed_on`) BETWEEN $Month AND $Month AND DAY(`changed_on`) BETWEEN $Day AND $Day ORDER BY ($SensorNamen[$i] IS NULL), $SensorNamen[$i]
Als ik maand nodig heb laat ik DAY weg en voor jaar laat ik DAY en MONTH weg.
Met dit soort query duurt het behoorlijk lang voordat ik alle gegevens heb.
Nu heb ik een andere query opbouw gevonden waar ik het ook mee voor elkaar heb, deze query ziet er als volgt uit:
SELECT MIN( temperatuur ) AS minRate, (
SELECT changed_on
FROM sensorwaardes
WHERE temperatuur = MIN( co.temperatuur )
AND day( changed_on ) = day( co.changed_on )
AND YEAR( changed_on ) = 2015
LIMIT 1
) AS min_date,
MAX( temperatuur ) AS maxRate, (
SELECT changed_on
FROM sensorwaardes
WHERE temperatuur = MAX( co.temperatuur )
AND day( changed_on ) = day( co.changed_on )
AND YEAR( changed_on ) = 2015
LIMIT 1
) AS max_date
FROM sensorwaardes co
WHERE YEAR( changed_on ) = 2015
GROUP BY YEAR(changed_on),day( changed_on )
In deze query staat nu maar 1 variabelen, maar op deze manier kan ik alle variabelen erin zetten en deze is veel sneller.
Ik ben nog vrij nieuw met MySQL en ik zie dat er veel tijdwinst te halen is in de opbouw van de query. Ik vraag me nu af kan het op een nog snellere manier en hoe werkt de laatste query?
voor elke dag een min + max temp?
of over een periode de min en max temp en op welke datum(s) die optraden? (wel of niet met dubbelen?)
SELECT MIN( temperatuur ) AS minRate, (
SELECT changed_on
FROM sensorwaardes
WHERE temperatuur = MIN( co.temperatuur )
AND day( changed_on ) = 10
AND MONTH( changed_on ) = 3
AND YEAR( changed_on ) = 2015
LIMIT 1
) AS min_date,
MAX( temperatuur ) AS maxRate, (
SELECT changed_on
FROM sensorwaardes
WHERE temperatuur = MAX( co.temperatuur )
AND day( changed_on ) = 10
AND MONTH( changed_on ) = 3
AND YEAR( changed_on ) = 2015
LIMIT 1
) AS max_date
FROM sensorwaardes co
WHERE YEAR( changed_on ) = 2015 AND day( changed_on ) = 10 AND MONTH( changed_on ) = 3
GROUP BY YEAR(changed_on),day( changed_on )
Wat ik wil is van bijvoorbeeld een dag de min en max en op welke datum en tijd deze optraden. Dubbelen zijn niet nodig het moet de eerste zijn die voorkomt.
1 maart 10 gr
2 maart 1 gr
2 maart 11 gr
2 maart 10 gr
3 maart 2 gr
3 maart 11 gr
Wat wil je als antwoord hebben als je bovenstaande data hebt?
1 gr 1 maart
en 11 gr 2 maart
of iets anders?
Dag min = 2 gr
Dag max = 11 gr
Gister min = 1 gr
Gister Max = 11 gr
Maand min = 1 gr ( 1 maart)
Maand max = 11 gr (2 maart)
Code (php)
1
2
2
WHERE YEAR( changed_on ) = 2015 AND day( changed_on ) = 10 AND MONTH( changed_on ) = 3
GROUP BY YEAR(changed_on),day( changed_on )
GROUP BY YEAR(changed_on),day( changed_on )
Functies maken queries traag en deze vijf functies maken een query nog evenredig veel trager. Je kunt hier gewoon een datum selecteren. De GROUP BY per dag kan er dan ook uit aangezien je de selectie toch tot één dag hebt beperkt:
Code (php)
1
WHERE DATE(changed_on) = '2015-03-10'
Sjaak Pieters op 11/03/2015 10:01:27:
Wat ik wil is van bijvoorbeeld een dag de min en max en op welke datum en tijd deze optraden. Dubbelen zijn niet nodig het moet de eerste zijn die voorkomt.
In dat geval moeten alle WHERE-clausules die één specifieke datum selecteren er juist uit en en groepeer je bijvoorbeeld per dag met DATE(), per maand met MONTH() of per jaar met YEAR(). De dagminima voor 2015 worden dan bijvoorbeeld:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT
MIN(temperatuur) AS min_rate,
changed_on
FROM
sensorwaardes
WHERE
YEAR(changed_on) = 2015
GROUP BY
DATE(changed_on)
MIN(temperatuur) AS min_rate,
changed_on
FROM
sensorwaardes
WHERE
YEAR(changed_on) = 2015
GROUP BY
DATE(changed_on)
Er staat me nog een topic bij van een tijdje geleden waarin het ook van belang was om alle datums waarom het minimum van de periode bereikt werd.
iets als "het minimum over die week was 2 graden en was op de 5e en de 7e maart"
Maar als dat niet van toepassing is, kan een simpele query zoals Ward hierboven geeft
Als ik hem uitvoer heb ik van 1 variabele de minimale waarde van de maand. De query duurde 0.0291 seconden
Als ik dan uitreken als ik voor alle 8 de variabelen de gegevens opvraag dan kom ik op:
8 x (4 x 2 ) x 0.0291 = 1.8624 seconden
Ivo P op 11/03/2015 11:18:12:
Wars query kan nog uitbreid worden met MAX en een max_rate om ook de max waarde op de datums op te halen.
Hoe kan ik deze uitbreiden met de max waarde? Is het sneller om alles in een query te stoppen?
Ivo P op 11/03/2015 11:18:12:
Er staat me nog een topic bij van een tijdje geleden waarin het ook van belang was om alle datums waarom het minimum van de periode bereikt werd.
iets als "het minimum over die week was 2 graden en was op de 5e en de 7e maart"
iets als "het minimum over die week was 2 graden en was op de 5e en de 7e maart"
Dit is niet nodig.
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
MIN(temperatuur) AS min_rate,
MAX(temperatuur) AS max_rate,
changed_on
FROM
sensorwaardes
WHERE
YEAR(changed_on) = 2015
GROUP BY
DATE(changed_on)
MIN(temperatuur) AS min_rate,
MAX(temperatuur) AS max_rate,
changed_on
FROM
sensorwaardes
WHERE
YEAR(changed_on) = 2015
GROUP BY
DATE(changed_on)
je zegt nog iets over 8 variabelen? ook andere sensorwaarden dan temperatuur?
Ivo P op 11/03/2015 11:52:02:
je zegt nog iets over 8 variabelen? ook andere sensorwaarden dan temperatuur?
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
SELECT
MIN(temperatuur) AS min_rate,
MAX(temperatuur) AS max_rate,
changed_on
FROM
sensorwaardes
WHERE
YEAR(changed_on) = 2015
GROUP BY
DATE(changed_on)
MIN(temperatuur) AS min_rate,
MAX(temperatuur) AS max_rate,
changed_on
FROM
sensorwaardes
WHERE
YEAR(changed_on) = 2015
GROUP BY
DATE(changed_on)
je zegt nog iets over 8 variabelen? ook andere sensorwaarden dan temperatuur?
Nu krijg ik:
min_rate max_rate changed_on
Ik heb dus voor de min_rate & max_rate een datum/tijd dit zouden twee verschillende moeten zijn.
Ja, ik ben met een weerstation bezig en heb 8 verschillende metingen.
Gewijzigd op 11/03/2015 11:57:41 door Sjaak Pieters
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
MIN(temperatuur) AS min_rate,
MAX(temperatuur) AS max_rate,
DATE(changed_on) AS datum
FROM
sensorwaardes
WHERE
YEAR(changed_on) = 2015
GROUP BY
datum
[size=xsmall][i]Toevoeging op 11/03/2015 12:43:01:[/i][/size]
dat kwam doordat je group-by niet bij het select stuk paste
MIN(temperatuur) AS min_rate,
MAX(temperatuur) AS max_rate,
DATE(changed_on) AS datum
FROM
sensorwaardes
WHERE
YEAR(changed_on) = 2015
GROUP BY
datum
[size=xsmall][i]Toevoeging op 11/03/2015 12:43:01:[/i][/size]
dat kwam doordat je group-by niet bij het select stuk paste
Voor wat de performance betreft: als je een grote dataset hebt, kun je voor statistieken data aggregeren. Na 23:59:59 uur vanavond veranderen de minimum- en maximumtemperatuur van vandaag nooit meer. Voor vergelijkingen per week, maand, jaar en dergelijke kun je daarmee een tweede tabel maken die nog slechts twee metingen per dag bevat.
Met andere woorden: je kunt het aantal aggregate functions dat je op de complete dataset loslaat drastisch beperken als je de uitkomsten opslaat.
Ward van der Put op 11/03/2015 12:52:52:
Kun je de tabeldefinitie posten? Misschien kan een extra index of een ander kolomtype hier en daar nog de snelheid verbeteren.
Voor wat de performance betreft: als je een grote dataset hebt, kun je voor statistieken data aggregeren. Na 23:59:59 uur vanavond veranderen de minimum- en maximumtemperatuur van vandaag nooit meer. Voor vergelijkingen per week, maand, jaar en dergelijke kun je daarmee een tweede tabel maken die nog slechts twee metingen per dag bevat.
Met andere woorden: je kunt het aantal aggregate functions dat je op de complete dataset loslaat drastisch beperken als je de uitkomsten opslaat.
Voor wat de performance betreft: als je een grote dataset hebt, kun je voor statistieken data aggregeren. Na 23:59:59 uur vanavond veranderen de minimum- en maximumtemperatuur van vandaag nooit meer. Voor vergelijkingen per week, maand, jaar en dergelijke kun je daarmee een tweede tabel maken die nog slechts twee metingen per dag bevat.
Met andere woorden: je kunt het aantal aggregate functions dat je op de complete dataset loslaat drastisch beperken als je de uitkomsten opslaat.
Ik heb een tabel met 9 kolommen bestaande uit:
PID
changed_on
Sensor 1
Sensor 2
Sensor 3
Sensor 4
Sensor 5
Sensor 6
Sensor 7
Sensor 8
Daar heb ik ook aan zitten denken, om de waarden alvast uit te rekenen. Dan met een cron task elke keer de gegevens om 00:00:00 laten genereren. Nu zit er alleen nog maar data van deze maand in de database, waarschijnlijk neemt de zoektijd toe naar mate de database groeit..
Gewijzigd op 11/03/2015 13:06:45 door Sjaak Pieters
Om deze te kunnen gebruiken moet je NOOIT functies over de kolom hebben in de where
Bijvoorbeeld:
Code (php)
1
.... WHERE YEAR(changed_on) = 2015
Kan je vervangen door:
Code (php)
1
.... WHERE changed_on >= CAST('2015-01-01' AS DATETIME)
Als je tabel in InnoDb staat is het zelfs beter om die AI primary key weg te halen en changed_on de PK te maken.
InnoDb tabellen hebben altijd één clustered index, en dat is de primary key tenzij je expliciet anders opgeeft. Aangezien je waarschijnlijk niets doet met dat niets zeggende nummertje en de invoer op oplopende tijden geschied is changed_on prima geschikt als PK.
Gewijzigd op 11/03/2015 14:13:31 door Ger van Steenderen