Query wordt na toevoegen order (te) langzaam
Twee tabellen.
Personen tabel (25000 records)
persid (Primary key)
voornaam
tussenvoegsel
achternaam (index)
Opstellingen tabel (370000 records)
Opstelkey (Primary key)
wid
Persid
volgorde
gesp
gesc
Code (php)
1
2
3
2
3
SELECT sum(o.gesc) as totgescoord,p.achternaam FROM opstelere1 o,personen1 p
where o.persid=p.persid
group by o.persid
where o.persid=p.persid
group by o.persid
Resultaat bovenstaande Query 6809 totaal, Query duurde 0,0176 seconden
Voeg ik echter een Order By toe dan wordt de Query echt een stuk trager.
Code (php)
1
2
3
4
2
3
4
SELECT sum(o.gesc) as totgescoord,p.achternaam FROM opstelere1 o,personen1 p
where o.persid=p.persid
group by o.persid
order by totgescoord desc
where o.persid=p.persid
group by o.persid
order by totgescoord desc
Resultaat Query 6809 totaal, Query duurde 2,7740 seconden
Query alleen op de Opstellingen tabel met het persid
Code (php)
1
2
3
4
2
3
4
SELECT o.persid,sum(o.gesc) as totgescoord FROM opstelere1 o
group by o.persid
order by totgescoord desc
group by o.persid
order by totgescoord desc
Resultaat 6809 totaal, Query duurde 0,1732 seconden
Maar ja, het is wel leuk om er een naam bij te hebben ;-)
Ik heb aardig wat gezocht op internet, maar ik kan de oplossing niet vinden.
Enige wat ik zie is dat het probleem zich vaker voordoet na het toevoegen van een sort, maar de oplossing?!?!
Explain zonder 'order by'
1 SIMPLE o NULL ALL persid NULL NULL NULL 371479 100.00 Using temporary; Using filesort
1 SIMPLE p NULL eq_ref PRIMARY PRIMARY 4 voetbals3.o.persid 1 100.00 NULL
Explain met 'order by'
1 SIMPLE o NULL ALL persid NULL NULL NULL 371479 100.00 Using temporary; Using filesort
1 SIMPLE p NULL eq_ref PRIMARY PRIMARY 4 voetbals3.o.persid 1 100.00 NULL
Er zou wel een index moeten zijn op de PK.
Als dat nog niet genoeg helpt zou ik beginnen met een (niet unieke) index op de kolom totgescoord.
Rob Doemaarwat op 07/03/2021 21:43:20:
Op je tabel "opstelere1" zit zo te zien geen enkele index. Ik zou in ieder geval op de kolom "persid" eens een index zetten (ivm de join naar de "personen1" tabel).
Tabel opstelere1 heeft indexen op;
Opstelkey (Primary Key)
Persid
Wid -> (is een wedstrijd id)
Toevoeging op 07/03/2021 22:09:29:
Ad Fundum op 07/03/2021 21:47:58:
Heeft een PK niet altijd een index? (Die gebruikt wordt bij FK constraints?)
Er zou wel een index moeten zijn op de PK.
Als dat nog niet genoeg helpt zou ik beginnen met een (niet unieke) index op de kolom totgescoord.
Er zou wel een index moeten zijn op de PK.
Als dat nog niet genoeg helpt zou ik beginnen met een (niet unieke) index op de kolom totgescoord.
PK zit een Index op. Is inderdaad standaard bij een PK.
totgescoord is een sum van kolom gesc. Op die kolom even een index gezet, maar ook dat maakt (helaas) geen verschil.
Code (php)
1
2
3
4
5
2
3
4
5
select x.*,(select achternaam from personen1 where persid = x.persid) from (
SELECT o.persid,sum(o.gesc) as totgescoord FROM opstelere1 o
group by o.persid
) x
order by totgescoord desc
SELECT o.persid,sum(o.gesc) as totgescoord FROM opstelere1 o
group by o.persid
) x
order by totgescoord desc
-----
Toch nog een poging tot een echte oplossing: kan het zijn dat de persid's van beide tabellen niet hetzelfde data type hebben (bij de ene bijvoorbeeld int, en de ander char). Dan gebruikt de join de index niet (voor beide regels van het explain plan zie je nl dat ie steeds door 100% vd records moet akkeren, en veel x veel = heel veel). Zorg dus dat beide kolommen hetzelfde datatype hebben (bijvoorbeeld int en int).
Gewijzigd op 07/03/2021 23:12:28 door Rob Doemaarwat
Je kunt proberen of het sneller te krijgen is met ORDER BY als een window function.
Of je moet een andere van werken voor deze code verzinnen, misschien door een tussenresultaat (zonder order by) op te slaan in een tijdelijk sessietabel in memory. Of als je het niet al te vaak uitrekent in een
Of domweg snellere hardware en meer geheugen toekennen aan MySQL / MariaDB.
Een andere oplossingsstrategie is de SUM per speler opslaan. Die verandert immers maar één keer, bij een nieuwe wedstrijduitslag, maar wordt mogelijk wel duizenden keren opgevraagd.
Rob Doemaarwat op 07/03/2021 23:08:15:
Zonder echte live data blijft het een beetje gokken wat de DB nou precies "van plan is". Maar is dit niet een quick+dirty fix:
-----
Toch nog een poging tot een echte oplossing: kan het zijn dat de persid's van beide tabellen niet hetzelfde data type hebben (bij de ene bijvoorbeeld int, en de ander char). Dan gebruikt de join de index niet (voor beide regels van het explain plan zie je nl dat ie steeds door 100% vd records moet akkeren, en veel x veel = heel veel). Zorg dus dat beide kolommen hetzelfde datatype hebben (bijvoorbeeld int en int).
Code (php)
1
2
3
4
5
2
3
4
5
select x.*,(select achternaam from personen1 where persid = x.persid) from (
SELECT o.persid,sum(o.gesc) as totgescoord FROM opstelere1 o
group by o.persid
) x
order by totgescoord desc
SELECT o.persid,sum(o.gesc) as totgescoord FROM opstelere1 o
group by o.persid
) x
order by totgescoord desc
-----
Toch nog een poging tot een echte oplossing: kan het zijn dat de persid's van beide tabellen niet hetzelfde data type hebben (bij de ene bijvoorbeeld int, en de ander char). Dan gebruikt de join de index niet (voor beide regels van het explain plan zie je nl dat ie steeds door 100% vd records moet akkeren, en veel x veel = heel veel). Zorg dus dat beide kolommen hetzelfde datatype hebben (bijvoorbeeld int en int).
Deze Query is al beduidend sneller (0,1887 seconden). Maar ik wil ook graag de voornaam en tussenvoegsel er bij hebben.
Als ik voornaam toevoeg krijg ik de melding
Operand behoort 1 kolommen te bevatten.
Wat zou ik dan nog meer aan de Query toe moeten voegen?
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
select x.*,
(select achternaam from personen1 where persid = x.persid) as achternaam
from
( SELECT o.persid,sum(o.gesc) as totgescoord FROM opstelere1 o
group by o.persid
) x
order by totgescoord desc
(select achternaam from personen1 where persid = x.persid) as achternaam
from
( SELECT o.persid,sum(o.gesc) as totgescoord FROM opstelere1 o
group by o.persid
) x
order by totgescoord desc
De persid's zijn in beide tabellen gelijk (int)
Toevoeging op 08/03/2021 20:33:53:
Ward van der Put op 08/03/2021 09:54:19:
Je kunt een WHERE-clausule toevoegen die alle `gesc` met een waarde van 0 of NULL negeert. Je wilt namelijk de SUM van `gesc` van hoog naar laag weten, dus daarvoor zijn alle nullen niet relevant.
Een andere oplossingsstrategie is de SUM per speler opslaan. Die verandert immers maar één keer, bij een nieuwe wedstrijduitslag, maar wordt mogelijk wel duizenden keren opgevraagd.
Een andere oplossingsstrategie is de SUM per speler opslaan. Die verandert immers maar één keer, bij een nieuwe wedstrijduitslag, maar wordt mogelijk wel duizenden keren opgevraagd.
'gesç'=0 scheelt zeker iets.
SUM per speler opslaan is eigenlijk geen optie. Ik sanp het idee. Maar de bedoeling is om de Query ook uit te breiden om te bepalen namens of tegen welke club een spelers heeft gescoord. Dit door een koppeling te maken tussen een wedstrijden tabel (wid) en de opstelling tabel (wid).
Dan is alleen opslaan van de SUM (en deze updaten) na iedere wedstrijd toch niet de oplossing.
Toevoeging op 08/03/2021 20:44:42:
Ad Fundum op 08/03/2021 08:58:41:
Ik had niet goed gekeken. Je doet een ORDER BY op een berekende kolom zonder index. Dat duurt vanzelfsprekend langer. MySQL / MariaDB (PostgreSQL accepteert bovenstaande syntax niet) moet dan 2x door de data ploegen, waarbij de 2e keer in een tijdelijk resultaat zonder index.
Je kunt proberen of het sneller te krijgen is met ORDER BY als een window function.
Of je moet een andere van werken voor deze code verzinnen, misschien door een tussenresultaat (zonder order by) op te slaan in een tijdelijk sessietabel in memory. Of als je het niet al te vaak uitrekent in eenMATERIALIZED VIEW tussentabel.
Of domweg snellere hardware en meer geheugen toekennen aan MySQL / MariaDB.
Je kunt proberen of het sneller te krijgen is met ORDER BY als een window function.
Of je moet een andere van werken voor deze code verzinnen, misschien door een tussenresultaat (zonder order by) op te slaan in een tijdelijk sessietabel in memory. Of als je het niet al te vaak uitrekent in een
Of domweg snellere hardware en meer geheugen toekennen aan MySQL / MariaDB.
MySQL / MariaDB (PostgreSQL accepteert bovenstaande syntax niet) moet dan 2x door de data ploegen, waarbij de 2e keer in een tijdelijk resultaat zonder index.
Wel jammer dat MySql
Dat is toch wel jammer. Op het oog een simpele (en in mijn ogen een eenvoudige Query) die niet voldoet.
Ik ga nog eens kijken naar die Window Functie én naar een andere code kijken.
Met die code van Rob Doemaarwat ben ik al een eind op weg. Maar nog net niet helemaal ;-(
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT o2.totgescoord,
p.achternaam
FROM ( SELECT persid, SUM(gesc) AS totgescoord
FROM opstelere1
GROUP BY persid ) AS o2
JOIN personen1 AS p ON p.persid = o2.persid
ORDER BY totgescoord DESC
p.achternaam
FROM ( SELECT persid, SUM(gesc) AS totgescoord
FROM opstelere1
GROUP BY persid ) AS o2
JOIN personen1 AS p ON p.persid = o2.persid
ORDER BY totgescoord DESC
Deze code en onderstaande index toegevoegd.
opstelere1 INDEX(persid, gesc)
Query duurde 0,1259 seconden
De query is nog wat traag, dat zal komen door de ORDER BY, die moet in de tussentabel (het resultaat van de binnenste query) 25000 rijen sorteren zonder index. Je kunt het verschil meteen zien door de ORDER BY weg te laten, ik gok dat de de query dan een factor 10 sneller zal zijn.
Ad Fundum op 10/03/2021 10:55:42:
Een index toevoegen maakt een query niet per definitie sneller, het is alleen zinvol als je hoogstens zo'n 15% van de data ophaalt, anders is er ook een kans dat een index de query trager maakt.
De query is nog wat traag, dat zal komen door de ORDER BY, die moet in de tussentabel (het resultaat van de binnenste query) 25000 rijen sorteren zonder index. Je kunt het verschil meteen zien door de ORDER BY weg te laten, ik gok dat de de query dan een factor 10 sneller zal zijn.
De query is nog wat traag, dat zal komen door de ORDER BY, die moet in de tussentabel (het resultaat van de binnenste query) 25000 rijen sorteren zonder index. Je kunt het verschil meteen zien door de ORDER BY weg te laten, ik gok dat de de query dan een factor 10 sneller zal zijn.
Met of zonder ORDER BY maakt nauwelijks verschil.
Wat soms helpt is om de tabel opnieuw aan te maken via kopiëren met SQL. Na veel mutaties blijft de nodige lucht in de tabel hangen. Zo worden rijenintern niet daadwerkelijk gewist, alleen gemarkeerd als gewist om de snelheid er in te houden. PostgreSQL stofzuigt de tabellen automatisch, je zou eens kunnen kijken of dat voor de database die jij gebruikt ook bestaat.
Een ander ding om te controleren is of de storage engine niet efficiënter kan zijn. In MySQL / MariaDB kan je misschien experimenteren met andere engines als MyISAM?
Als niets helpt zou je toch eens moeten gaan kijken naar het opschalen van de (eventueel virtuele) hardware.
Als ik die EXPLAIN bekijk staat er dat gebruik word gemaakt van filesort, dat is een vrij langzame methode.
Waardoor dit komt is ietwat ingewikkeld maar het komt er op neer dat het resultaat niet in het beschikbare geheugen past en in een tijdelijke tabel word geplaatst. Aan het einde van de query worden deze tijdelijke tabellen via een merge-sort gecombineerd en teruggegeven. (meer info hier: https://www.percona.com/blog/2009/03/05/what-does-using-filesort-mean-in-mysql/ en hier: https://petrunia.net/2007/08/29/how-mysql-executes-order-by/).
Opschalen van beschikbaar geheugen voor de InnoDB engine zou dat probleem moeten oplossen, het beperken van de resultaatgrootte ook.
Als laatste, maar dat is iets wat je moet testen m.b.v. profiling, kun je de query opknippen in meerdere queries met kleinere resultsets (aan de PHP kant) en ze daar combineren. Minder mooi maar dat kan in sommige gevallen efficiënter zijn.