Snelheid van queries

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Sjaak Pieters

Sjaak Pieters

11/03/2015 09:45:09
Quote Anchor link
Hoi,

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?
 
PHP hulp

PHP hulp

28/03/2024 20:56:42
 
Ivo P

Ivo P

11/03/2015 09:51:36
Quote Anchor link
wat probeer je nu weer te geven?

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?)
 
Sjaak Pieters

Sjaak Pieters

11/03/2015 10:01:27
Quote Anchor link
De laatste query is inderdaad niet de goede, deze geeft alle dagen min/max. Degene die ik gebruik is deze:

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.
 
Ivo P

Ivo P

11/03/2015 10:40:16
Quote Anchor link
1 maart 1 gr
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?
 
Sjaak Pieters

Sjaak Pieters

11/03/2015 10:46:19
Quote Anchor link
Stel dat het 3 maart is, dan wil ik:

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)
 
Ward van der Put
Moderator

Ward van der Put

11/03/2015 11:01:31
Quote Anchor link
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
WHERE YEAR( changed_on ) = 2015 AND day( changed_on ) = 10 AND MONTH( changed_on ) = 3
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)
PHP script in nieuw venster Selecteer het PHP script
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)
PHP script in nieuw venster Selecteer het PHP script
1
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)
 
Ivo P

Ivo P

11/03/2015 11:18:12
Quote Anchor link
Wars query kan nog uitbreid worden met MAX en een max_rate om ook de max waarde op de datums op te halen.

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
 
Sjaak Pieters

Sjaak Pieters

11/03/2015 11:40:52
Quote Anchor link
Bedankt Ward. Ik vraag me alleen af of deze query sneller is.
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"


Dit is niet nodig.
 
Ivo P

Ivo P

11/03/2015 11:52:02
Quote Anchor link
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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)


je zegt nog iets over 8 variabelen? ook andere sensorwaarden dan temperatuur?
 
Sjaak Pieters

Sjaak Pieters

11/03/2015 11:55:48
Quote Anchor link
Ivo P op 11/03/2015 11:52:02:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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)


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
 
Ivo P

Ivo P

11/03/2015 12:42:22
Quote Anchor link
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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
 
Ward van der Put
Moderator

Ward van der Put

11/03/2015 12:52:52
Quote Anchor link
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.
 
Sjaak Pieters

Sjaak Pieters

11/03/2015 13:05:56
Quote Anchor link
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.


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
 
Ger van Steenderen
Tutorial mod

Ger van Steenderen

11/03/2015 14:13:06
Quote Anchor link
Allereerst moet je een index zetten op changed_on.
Om deze te kunnen gebruiken moet je NOOIT functies over de kolom hebben in de where
Bijvoorbeeld:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
.... WHERE YEAR(changed_on) = 2015

Kan je vervangen door:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.