Query wordt na toevoegen order (te) langzaam

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Jan van Veen

Jan van Veen

06/03/2021 23:33:39
Quote Anchor link
Ik heb het volgende probleem.

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)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
SELECT sum(o.gesc) as totgescoord,p.achternaam FROM opstelere1 o,personen1 p
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)
PHP script in nieuw venster Selecteer het PHP script
1
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

Resultaat Query 6809 totaal, Query duurde 2,7740 seconden

Query alleen op de Opstellingen tabel met het persid
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
SELECT o.persid,sum(o.gesc) as totgescoord FROM opstelere1 o

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?!?!
 
PHP hulp

PHP hulp

14/05/2021 03:22:15
 
Rob Doemaarwat

Rob Doemaarwat

07/03/2021 19:31:54
Quote Anchor link
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.
 
R R

R R

07/03/2021 21:29:41
Quote Anchor link
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
 
Rob Doemaarwat

Rob Doemaarwat

07/03/2021 21:43:20
Quote Anchor link
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).
 
Ad Fundum

Ad Fundum

07/03/2021 21:47:58
Quote Anchor link
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.
Gewijzigd op 07/03/2021 21:48:17 door Ad Fundum
 
R R

R R

07/03/2021 22:03:38
Quote Anchor link
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.

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.
 
Rob Doemaarwat

Rob Doemaarwat

07/03/2021 23:08:15
Quote Anchor link
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:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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

-----

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
 
Ad Fundum

Ad Fundum

08/03/2021 08:58:41
Quote Anchor link
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.
 
Ward van der Put
Moderator

Ward van der Put

08/03/2021 09:54:19
Quote Anchor link
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.
 
Jan van Veen

Jan van Veen

08/03/2021 17:43:42
Quote Anchor link
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:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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

-----

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)
PHP script in nieuw venster Selecteer het PHP script
1
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


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.

'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 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 ;-(
 
Jan van Veen

Jan van Veen

09/03/2021 21:18:26
Quote Anchor link
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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


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

Query duurde 0,1259 seconden
 
- Ariën -
Beheerder

- Ariën -

09/03/2021 21:49:20
Quote Anchor link
Dat is een flinke performance-boost :-)
 
Ad Fundum

Ad Fundum

10/03/2021 10:55:42
Quote Anchor link
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.
 
Jan van Veen

Jan van Veen

10/03/2021 22:37:00
Quote Anchor link
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.

Met of zonder ORDER BY maakt nauwelijks verschil.
 
Ad Fundum

Ad Fundum

11/03/2021 15:34:51
Quote Anchor link
Dan moet I/O de bottleneck zijn.

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.
 
Thom nvt

Thom nvt

12/03/2021 07:40:05
Quote Anchor link
Ik denk dat Ad Fundum gelijk heeft in het opschalen van de hardware, of iig de toegewezen hoeveelheid geheugen voor MySQL.
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.
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.