[MySQL] sum() + max + group by; trage query

[bartbh]

In navolging van een eerder topic met een andere vraag (MySQL totaal berekenen over tijdspanne van 1 uur), ben ik nu tegen een nieuw probleem aangelopen.

Voor een systeem waar ik mee aan het stoeien ben, heb ik een database opgezet waarin gelogd wordt hoeveel energie er opgewekt is door de zonnepanelen. Elke 5 min loggen de systemen naar een tabel en schrijven daar hun (tot op dat moment van de dag) hoeveelheid opgewekte energie op en hun totale (lifetime) opgewekte energie.

Nu wil ik berekenen hoeveel energie alle systemen bij elkaar opgewekt hebben. Dit wil ik doen door voor elk systeem de maximale gelogde energiewaarde te nemen en dat allemaal bij elkaar op te tellen.

De tabel ziet er als volgt uit: (vereenvoudigd voorbeeld, echte bevat 40 kolommen en 250.000 records so far)

code:

id | id_system | time (datetime) | energy | energy_total

Hiervoor gebruik ik de volgende query:

MySQL:

SELECT SUM(energy_total) as energy_total 
FROM (
    SELECT MAX(energy_total) as energy_total 
    FROM data_running 
    GROUP BY id_system
) as subtable

Het probleem is echter dat deze query er tussen de 8 en 10 seconden over doet om een resultaat terug te geven. Zodra de query in de cache zit gaat het uiteraard beter, maar elke 5 minuten wordt deze weer geleegd dus dat biedt geen soelaas.

Is er een betere manier om dit totaal te berekenen? Of heeft iemand anders tips qua performance?

[Voutloos]

Index op energy_total en id_system, welke volgorde moet je even zelf checken dmv EXPLAIN.

[bartbh]

Inderdaad, wat een wereld van verschil!

De eerste keer dat ik met zo'n grote dataset werk.

Thanks!

[GlowMouse]

bartbh schreef op dinsdag 06 augustus 2013 @ 15:07:
Inderdaad, wat een wereld van verschil!

De eerste keer dat ik met zo'n grote dataset werk.

Thanks!

Welke index heb je uiteindelijk aangemaakt?

[bartbh]

Index op id_system en energy_total.

[GlowMouse]

Dat is inderdaad de goede.

[bartbh]

Zodat ik er ook nog wat van leer, wáárom is dat de goede?

[GlowMouse]

Je moet de index zien als het resultaat van deze query:

SELECT id_system,energy_total
FROM data_running
ORDER BY id_system,energy_total

Je kunt daar per id_system snel de grootste energy_total vinden.