Toon posts:

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

Pagina: 1
Acties:

Onderwerpen

Vraag


  • RickyHeijnen
  • Registratie: maart 2005
  • Laatst online: 04-12 09:54
Om een grafiekje te plotten met omzet per maand gebruik ik deze query:
SQL:
1
2
3
4
5
6
7
8
9
10
11
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]

Beste antwoord (via RickyHeijnen op 07-01-2019 09:36)


  • Knutselsmurf
  • Registratie: december 2000
  • Laatst online: 20:56

Knutselsmurf

LED's make things better

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:

DatumJaarMaandDag
2018-12-3020181230
2018-12-3120181231
2019-01-01201911


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.

- This line is intentionally left blank -

Alle reacties


  • The Eagle
  • Registratie: januari 2002
  • Laatst online: 21:48

The Eagle

I wear my sunglasses at night

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:
1
2
3
4
5
6
7
8
9
10
11
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

Al is het nieuws nog zo slecht, het wordt leuker als je het op zijn Brabants zegt :)


  • RobIII
  • Registratie: december 2001
  • Laatst online: 22:13

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

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]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


Acties:
  • Beste antwoord
  • +3Henk 'm!

  • Knutselsmurf
  • Registratie: december 2000
  • Laatst online: 20:56

Knutselsmurf

LED's make things better

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:

DatumJaarMaandDag
2018-12-3020181230
2018-12-3120181231
2019-01-01201911


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.

- This line is intentionally left blank -


  • MSteverink
  • Registratie: juni 2004
  • Laatst online: 20:26
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
  • Registratie: december 2001
  • Laatst online: 22:13

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

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 21e 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]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


  • BarôZZa
  • Registratie: januari 2003
  • Laatst online: 22:02
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 21e 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
  • Registratie: december 2009
  • Laatst online: 14:54

MrMonkE

★☆Bananarama fan☆★

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?

"Identicon"? More like "Obfuscaton".


  • BarôZZa
  • Registratie: januari 2003
  • Laatst online: 22:02
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
  • Registratie: december 2001
  • Laatst online: 22:13

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

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 8)7 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]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


  • BarôZZa
  • Registratie: januari 2003
  • Laatst online: 22:02
RobIII schreef op vrijdag 4 januari 2019 @ 17:37:
[...]

Are you sure? En, if so, OMFG 8)7 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
  • Registratie: december 2001
  • Laatst online: 22:13

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

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

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]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


  • Kalentum
  • Registratie: juni 2004
  • Laatst online: 22:00
Die expressie rechts van de > wordt inderdaad maar 1x geevalueerd

PVoutput


  • RobIII
  • Registratie: december 2001
  • Laatst online: 22:13

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

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

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]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Roses are red Violets are blue, Unexpected ‘{‘ on line 32.

Over mij


  • The Eagle
  • Registratie: januari 2002
  • Laatst online: 21:48

The Eagle

I wear my sunglasses at night

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:

DatumJaarMaandDag
2018-12-3020181230
2018-12-3120181231
2019-01-01201911


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 :)

Al is het nieuws nog zo slecht, het wordt leuker als je het op zijn Brabants zegt :)


  • RickyHeijnen
  • Registratie: maart 2005
  • Laatst online: 04-12 09:54
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:

DatumJaarMaandDag
2018-12-3020181230
2018-12-3120181231
2019-01-01201911


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.
Pagina: 1


Nintendo Switch (OLED model) Apple iPhone 13 LG G1 Google Pixel 6 Call of Duty: Vanguard Samsung Galaxy S21 5G Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2021 Hosting door True

Tweakers maakt gebruik van cookies

Bij het bezoeken van het forum plaatst Tweakers alleen functionele en analytische cookies voor optimalisatie en analyse om de website-ervaring te verbeteren. Op het forum worden geen trackingcookies geplaatst. Voor het bekijken van video's en grafieken van derden vragen we je toestemming, we gebruiken daarvoor externe tooling die mogelijk cookies kunnen plaatsen.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Forum cookie-instellingen

Bekijk de onderstaande instellingen en maak je keuze. Meer informatie vind je in ons cookiebeleid.

Functionele en analytische cookies

Deze cookies helpen de website zijn functies uit te voeren en zijn verplicht. Meer details

janee

    Cookies van derden

    Deze cookies kunnen geplaatst worden door derde partijen via ingesloten content en om de gebruikerservaring van de website te verbeteren. Meer details

    janee