MySQL slechte performance GROUP BY YEAR(), MONTH()

[RickyHeijnen]

Om een grafiekje te plotten met omzet per maand gebruik ik deze query:

SQL:

BEGIN
SELECT
    SUM(cpi.amount*cpi.billprice) AS 'total',
    MONTH(cp.created) as 'month',
    YEAR(cp.created) as 'year'
FROM
    clientpackage cp
INNER JOIN clientpackageitem cpi ON cp.clientpackage_id = cpi.clientpackage_id
WHERE
    YEAR(cp.created) > YEAR(DATE_SUB(NOW(), INTERVAL 4 YEAR))
GROUP BY MONTH(cp.created), YEAR(cp.created);

Het nadeel is alleen dat deze query ruim 15 seconden duurt. Als ik de GROUP BY regel weghaal dan duurt de query nog maar 0,015 seconden en levert 560.000 rijen op.

Uiteraard zit er een index op de kolom cp.created maar die wordt niet gebruikt omdat hij de PRIMARY KEY die op cp.clientpackageitem_id zit gebruikt.

Na wat Googlen kom ik al een aantal keer het antwoord "Summary tables" tegen. Nu ben ik daar in eerste instantie niet zo'n fan van omdat het een stuk meer aanpassingen in de applicatie vergt en ik twijfel of dat echt de beste oplossing is.

Waar zou ik nog naar kunnen kijken om de performance van de query te verbeteren?

[ Voor 7% gewijzigd door RickyHeijnen op 04-01-2019 14:33 ]

[The Eagle]

Ik zou eens beginnen bij je explain plan om te kijken wat ie onder water nou doet

Wilde gok: voor iedere rij die ie tegenkomt moet ie de MONTH en YEAR functie afvuren, en dat kost extra tijd. Zonder de group by hoeft ie dat niet. Maar kun geen group by op de alias doen? Moet je alleen de alias even aanpassen, want nu gebruik je gereserveerde functienamen en ik vermoed dat ie dat sowieso niet pikt
Aangepaste code:

SQL:

BEGIN
SELECT
    SUM(cpi.amount*cpi.billprice) AS 'total',
    MONTH(cp.created) as 'created_month',
    YEAR(cp.created) as 'created_year'
FROM
    clientpackage cp
INNER JOIN clientpackageitem cpi ON cp.clientpackage_id = cpi.clientpackage_id
WHERE
    created_year > YEAR(DATE_SUB(NOW(), INTERVAL 4 YEAR))
GROUP BY created_month, created_year;

Alternatief: maak een function based index achtig iets aan. Zie https://dev.mysql.com/doc...-index-optimizations.html

[RobIII]

Wat @The Eagle zegt klopt; per rij moet de MONTH en YEAR functie aangeroepen worden en dat is niet gratis. Wat jij wil is een materialized view; google dat maar.

@The Eagle zolang de aliassen netjes escaped worden met backticks kun je die reserved words prima gebruiken.

[ Voor 25% gewijzigd door RobIII op 04-01-2019 16:12 ]

[Knutselsmurf]

Helaas heeft MySQL geen Materialized Views, dus zullen er andere kunstgrepen nodig zijn.

Een optie die ik zelf wel eens heb gebruikt voor dergelijke situaties, is het gebruik van een Datum-hulptabel:

Datum	Jaar	Maand	Dag
2018-12-30	2018	12	30
2018-12-31	2018	12	31
2019-01-01	2019	1	1

Je kunt deze tabel dan van indexes op de diverse kolommen voorzien.Als je dan je cp.created joined op deze datum-tabel, kun je gebruik maken van die indexen.

Je kunt later naar behoeven deze datum-tabel vervolgens nog uitbreiden met extra kolommen, zoals bijvoorbeeld weekdag/ weeknr, etc.

[MSteverink]

Kun je maand en jaar niet als generated kolommen (https://dev.mysql.com/doc...le-generated-columns.html) in de tabel (cp) zetten? Dan zit de pijn bij het invoeren van de records, niet bij het opvragen.

Ik vraag me wel af of een wachttijd van 15 seconden wel zo'n probleem is bij een grafiek die je maar één keer maand hoeft op te vragen.

[RobIII]

Knutselsmurf schreef op vrijdag 4 januari 2019 @ 16:34:
Een optie die ik zelf wel eens heb gebruikt voor dergelijke situaties, is het gebruik van een Datum-hulptabel:

Dat werkt wel alleen maar 'lekker' als je rij dan ook alleen maar een date en geen datetime bevat.

Wat ik uit TS begrijp is het voor een overzicht per maand; je kunt dus best een cronjob o.i.d. starten op de eerste van de maand om alle data 'voorgekauwd' in een tabel te zetten (en daarmee je 'materialized view' te maken). Nee, het is dan geen échte materialized view maar je ligt dan ook niet te rommelen met velden die niet in de originele ("onderliggende") tabel horen of met datumtabellen. En tzt, wanneer MySQl eens besluit om in de 21^e eeuw toe te treden, hoef je alleen maar je 'materialized view' om te bouwen naar een échte materialized view zonder dat je applicatie of wat dan ook daar verder iets van merkt. Het houdt je datamodel zuiver(der).

[ Voor 15% gewijzigd door RobIII op 04-01-2019 16:57 ]

[BarôZZa]

Helpt dit?

- edit: nah dit werkt niet -

YEAR(cp.created) > YEAR(DATE_SUB(NOW(), INTERVAL 4 YEAR)) is ook niet handig, want dan pakt hij de index van cp.created niet. cp.created > '2015-01-01' is dan sneller.

RobIII schreef op vrijdag 4 januari 2019 @ 16:53:
[...]

Dat werkt wel alleen maar 'lekker' als je rij dan ook alleen maar een date en geen datetime bevat.

Wat ik uit TS begrijp is het voor een overzicht per maand; je kunt dus best een cronjob o.i.d. starten op de eerste van de maand om alle data 'voorgekauwd' in een tabel te zetten (en daarmee je 'materialized view' te maken). Nee, het is dan geen échte materialized view maar je ligt dan ook niet te rommelen met velden die niet in de originele ("onderliggende") tabel horen of met datumtabellen. En tzt, wanneer MySQl eens besluit om in de 21^e eeuw toe te treden, hoef je alleen maar je 'materialized view' om te bouwen naar een échte materialized view zonder dat je applicatie of wat dan ook daar verder iets van merkt. Het houdt je datamodel zuiver(der).

We doen hier iets soortgelijks, maar dan per dag. Dan kan je per dag/week/maand/jaar snel de totalen berekenen.

[ Voor 13% gewijzigd door BarôZZa op 04-01-2019 18:26 ]

[MrMonkE]

BarôZZa schreef op vrijdag 4 januari 2019 @ 17:27:
Helpt dit?

SELECT DATE_FORMAT(cp.created, '%m-%Y') AS group_by_date
..
GROUP BY group_by_date

Geen idee of die dubbele group by nog impact heeft.


[...]

We doen hier iets soortgelijks, maar dan per dag. Dan kan je per dag/week/maand/jaar snel de totalen berekenen.

Doe je dat met een insert/update trigger of 'met de hand' ergens in code?

[BarôZZa]

MrMonkE schreef op vrijdag 4 januari 2019 @ 17:30:
[...]


Doe je dat met een insert/update trigger of 'met de hand' ergens in code?

Cronjob per dag en een scriptje. Heb je overig wel dat de huidige dag niet meegeteld wordt, maar dat was voor de use case geen probleem.

[RobIII]

BarôZZa schreef op vrijdag 4 januari 2019 @ 17:27:
YEAR(cp.created) > YEAR(DATE_SUB(NOW(), INTERVAL 4 YEAR)) is ook niet handig, want dan pakt hij de index van cp.created niet.

Are you sure? En, if so, OMFG Dit is toch typisch iets wat je query planner prima kan optimizen. Ik heb nu even niet de mogelijkheid om 't te testen, anders had ik die bewering graag even geverifieerd.

[ Voor 13% gewijzigd door RobIII op 04-01-2019 18:18 ]

[BarôZZa]

RobIII schreef op vrijdag 4 januari 2019 @ 17:37:
[...]

Are you sure? En, if so, OMFG Dit is toch typisch iets wat je query planner prima kan optimizen. Ik heb nu even niet de mogelijkheid om 't te testen, anders had ik die bewering graag even geverifieerd.

Pretty sure.

De YEAR(cp.created) zorgt ervoor dat het voor elke record de YEAR() functie gecalled wordt met de datum om daarna pas de condition te checken.

Op m'n lokale dev omgeving:

SELECT * FROM `tabel` WHERE start_time > '2017-01-01'
Query took 0.0005 seconds.

SELECT * FROM `tabel` WHERE YEAR(start_time) >= 2017
Query took 0.0124 seconds.

de explain geeft ook aan dat er null possible indexes zijn.

Zou in dit geval niet al teveel uit moeten maken overigens, omdat ik vermoed dat de optimizer de join pas erna doet. De traagheid komt hier door de sum.

[ Voor 9% gewijzigd door BarôZZa op 04-01-2019 18:22 ]

[RobIII]

Ja, nee, sorry. My bad. Ik las YEAR(cp.created) als YEAR(<const>) i.p.v. YEAR(<field>). Dom.
He, nee, wat? Wacht. Moment hoor...

De expression YEAR(DATE_SUB(NOW(), INTERVAL 4 YEAR)) kan gewoon naar '2015-1-1' omgezet worden en dan kan 'ie toch prima een index gebruiken (theoretisch allerminst, of MySQL er zin in heeft is een tweede). Dan hoeft 'ie de YEAR functie helemaal niet te invoken.

[ Voor 74% gewijzigd door RobIII op 04-01-2019 18:21 ]

[Kalentum]

Die expressie rechts van de > wordt inderdaad maar 1x geevalueerd

[RobIII]

rutgerw schreef op vrijdag 4 januari 2019 @ 18:21:
Die expressie rechts van de > wordt inderdaad maar 1x geevalueers

Ja, maar 't gaat om de linkerkant hier; of die voor elke rij geevalueerd moet worden. En in de huidige expressie hoeft dat volgens mij niet. Maar ik zit effe (letterlijk) tussen de soep en de aardappelen half mee te lezen en te reageren dus ik praat er wellicht langs

Bij MONTH of DAY oid zou het een ander verhaal zijn. Maar goed, de query planner een handje helpen kan nooit kwaad natuurlijk.

[ Voor 11% gewijzigd door RobIII op 04-01-2019 18:25 ]

[The Eagle]

Knutselsmurf schreef op vrijdag 4 januari 2019 @ 16:34:
Helaas heeft MySQL geen Materialized Views, dus zullen er andere kunstgrepen nodig zijn.

Een optie die ik zelf wel eens heb gebruikt voor dergelijke situaties, is het gebruik van een Datum-hulptabel:

Datum	Jaar	Maand	Dag
2018-12-30	2018	12	30
2018-12-31	2018	12	31
2019-01-01	2019	1	1

Je kunt deze tabel dan van indexes op de diverse kolommen voorzien.Als je dan je cp.created joined op deze datum-tabel, kun je gebruik maken van die indexen.

Je kunt later naar behoeven deze datum-tabel vervolgens nog uitbreiden met extra kolommen, zoals bijvoorbeeld weekdag/ weeknr, etc.

Persoonlijk denk ik dat dat in dit geval de mooste oplossing is, gewoon een herbruikbare calender table aanmaken. En dan niet alleen met de dagen en maanden: https://gist.github.com/bryhal/4129042
Want dikke kans dat je dit vaker tegen gaat komen
En dan gewoon joinen op datum. Hoef je vermoedelijk niet eens extra indexen op die calendar table te zetten, want je joint toch primair op datum, wat tevens de pri key en dus al indexed is

[RickyHeijnen]

Knutselsmurf schreef op vrijdag 4 januari 2019 @ 16:34:
Helaas heeft MySQL geen Materialized Views, dus zullen er andere kunstgrepen nodig zijn.

Een optie die ik zelf wel eens heb gebruikt voor dergelijke situaties, is het gebruik van een Datum-hulptabel:

Datum	Jaar	Maand	Dag
2018-12-30	2018	12	30
2018-12-31	2018	12	31
2019-01-01	2019	1	1

Je kunt deze tabel dan van indexes op de diverse kolommen voorzien.Als je dan je cp.created joined op deze datum-tabel, kun je gebruik maken van die indexen.

Je kunt later naar behoeven deze datum-tabel vervolgens nog uitbreiden met extra kolommen, zoals bijvoorbeeld weekdag/ weeknr, etc.

Helemaal super! Een calendar table ertussen heeft de query teruggebracht naar 0,4 sec. Het klinkt heel logisch, maar ik kwam deze oplossing nergens tegen in mijn zoektocht via Google / Stackoverflow. En kan hem inderdaad heel goed hergebruiken voor tientallen andere grafiekjes die hierdoor ook worden geoptimaliseerd.