Query wordt na toevoegen order (te) langzaam

Zet eens "explain " voor je query. Je krijgt dan een "explain plan" = de manier waarop de database de query "aanpakt" (wat doet ie eerst, welke indexen gebruikt ie, enz). Mogelijk zie je dan heel snel wat er bij de trage versie anders gaat, en kun je daar op anticiperen (bijvoorbeeld een index toevoegen). En anders explain plan hier even dumpen (voor de versie met *en* zonder "order by"), kunnen wij eens kijken.

Ik heb dit gedaan in phpMyAdmin. Weet niet of dit zo leesbaar is. Maar ik zie geen verschil met of zonder 'order by'

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

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

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.

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)



[size=xsmall]Toevoeging op 07/03/2021 22:09:29:[/size]

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.

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.

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:

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

-----

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

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 een MATERIALIZED VIEW tussentabel.
Of domweg snellere hardware en meer geheugen toekennen aan MySQL / MariaDB.

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.

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:

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

-----

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?

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

De persid's zijn in beide tabellen gelijk (int)



[size=xsmall]Toevoeging op 08/03/2021 20:33:53:[/size]

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.

'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.




[size=xsmall]Toevoeging op 08/03/2021 20:44:42:[/size]

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 een MATERIALIZED VIEW tussentabel.
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 ;-(

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

Deze code en onderstaande index toegevoegd.
opstelere1 INDEX(persid, gesc)

Query duurde 0,1259 seconden