group by in mysql op een 4.5 Miljoen rijen tabel langzaam

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Daniel van Seggelen

Daniel van Seggelen

14/07/2020 18:33:51
Quote Anchor link
Hallo,

Ik heb dus een 4.5 Miljoen rows tabel.

Als ik dit uitvoer.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
SELECT * from pro_pr_spec WHERE cat_id != '' AND naam LIKE 'samsung%' AND winkel_id IN ('92') GROUP by pid LIMIT 0, 12


Dan duurt het 48-58 seconde.
Erg lang dus en de ajax call waarmee hij deze query uitvoert krijgt een "FAILED" mee.

Als ik de "group by pid" weghaal, dan is het 0.03 seconde. root verschil dus.

Ik heb pid een index, naam een FULLTEXT index gegeven, winkel_id en cat_id hebben ook een index.

Wat is nu de manier om dit sneller te laten werken?

Ik dacht aan een PHP array (van pid) waarbij ik eerst deze query ZONDER "GROUP by pid" uitvoer. Uiteindelijk wil ik dan de array uniek maken en deze terugsturen.

Zijn er betere manieren voor om dit te doen?

Groet

Daniel
Gewijzigd op 15/07/2020 04:40:25 door Daniel van Seggelen
 
PHP hulp

PHP hulp

28/03/2024 09:58:49
 
- Ariën  -
Beheerder

- Ariën -

14/07/2020 19:09:58
Quote Anchor link
Kan je de query ook eens uitvoeren in je SQL-client door er EXPLAIN voor te zetten?
Wat voor informatie zie je dan allemaal?
 
Thomas van den Heuvel

Thomas van den Heuvel

14/07/2020 19:51:22
Quote Anchor link
En wat is cat_id dan precies? Tekst? Als het een auto increment nummer was zou ik WHERE cat_id IS NOT NULL verwachten?
 
Daniel van Seggelen

Daniel van Seggelen

15/07/2020 04:39:45
Quote Anchor link
Met explain krijg ik dit terug:

Quote:
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
pro_pr_spec
index
cat_id,wed,naawid,naam
pid
4
NULL
658
Using where



cat_id is een integer(6)

Toevoeging op 15/07/2020 04:42:28:

Het zijn dus 4.5 Miljoen rijen. Als het er maar 100 zijn, werkt het prima.
Bij de PHP array moet ik wel steeds 12 resultaten terug sturen.
 
Ward van der Put
Moderator

Ward van der Put

15/07/2020 07:54:21
Quote Anchor link
Daniel van Seggelen op 14/07/2020 18:33:51:
Als ik de "group by pid" weghaal, dan is het 0.03 seconde. root verschil dus.

Dat klopt ook: zoals bijvoorbeeld hier wordt uitgelegd, kun je GROUP BY niet gebruiken bij een SELECT * zonder aggregate function.

De GROUP BY pid moet voor élke pid in de tabel bepalen of er kan worden gegroepeerd met een identieke pid. Is die pid bijvoorbeeld een unieke product-ID? Dan heeft groeperen ook geen zin en kun je de GROUP BY pid gewoon weglaten.

Verder kan de afhandeling van integers efficiënter:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
SELECT *
  FROM pro_pr_spec
 WHERE cat_id IS NOT NULL
   AND winkel_id = 92
   AND naam LIKE 'samsung%'
 LIMIT 0, 12
 
Daniel van Seggelen

Daniel van Seggelen

15/07/2020 08:17:50
Quote Anchor link
DIt zijn prijzen van verschillende winkels met dezelfde producten. Dus pid is niet identiek in deze tabel, maar het is en unieke product_id. Het resultaat moet dus een unieke pid zijn vanuit een tabel waar hij meerdere keren voorkomt, vandaar dat ik de group by pid gebruik.
HTE resultaat is goed, maar duurt gewoon ontzettend lang.

Ik heb zoals ik lees geen aggregate function nodig.



Toevoeging op 15/07/2020 08:19:33:

Of de hele database structuur moet anders,dus bijv 1 pid rijd met een field van een JSON array met ale winkels die dit product aanbieden bijv?

Toevoeging op 15/07/2020 08:40:22:

Ik gebruik dit nu maar, is 2 sec, maar scheelt een hoop:

SELECT DISTINCT pid , winkel_id, cat_id FROM pro_pr_spec WHERE cat_id != '0' AND winkel_id = 92 AND naam LIKE 'samsung%' LIMIT 0, 12
Gewijzigd op 15/07/2020 08:41:16 door Daniel van Seggelen
 
Ivo P

Ivo P

15/07/2020 18:40:24
Quote Anchor link
". Het resultaat moet dus een unieke pid zijn vanuit een tabel waar hij meerdere keren voorkomt, vandaar dat ik de group by pid gebruik."

Dat kan dus in Mysql, maar is een raar resultaat.
Je vraagt dan aan je database om de hele lijst te bekijken, met alle details per record, maar een pid mag maar 1 keer terug komen.

Dat is zo iets als een opdracht aan jou om me een lijstje te geven van alle details van de cola in de supermarkt, maar ik wens maar 1 x het product cola in het antwoord te zien.

dus
cola coca 1.75 regular
cola coca 1.85 light
cola pepsi 1.65 zero

moet platgeslagen worden tot

cola ... ... ...
en op de puntjes moet dan wat komen te staan? coca? of "pepsi"
en welke prijs dan? of welke uitvoering?

Daar kan de database dus eigenlijk geen zinnig antwoord op geven. Als je mysql een beetje vergevingsgezind instelt, dan krijg je geen antwoord maar zou er zo maar

cola pepsi 1.75 light

als antwoord uit kunnen komen. Een combinatie die random lijkt (mogelijk afhankelijk van de volgorde van invoeren?) en waar je sowieso niets mee kunt.

Iets als "geef me per drank (cola) de hoogste prijs" zou wel kunnen. Maar dan heb je een aggregatie functie (max) nodig.

Je probeert hier een functionatliteit te misbruiken die eigenlijk een bug is.

En als je DISTINCT nodig hebt, dan heb je eigenlijk ook je data al niet netjes genormaliseerd opgeslagen.
Je zou een aparte tabel moeten hebben met je producten.
En per product_id zou je dan een prijs per leverancier moeten hebben.

product_id, leverancier_id, prijs.

En die naam (samsung) zou al bij het product moeten staan. En dan liefst al als fabrikantnaam.

zoeken met like zou je moeten zien te voorkomen
 
Ad Fundum

Ad Fundum

16/07/2020 14:54:40
Quote Anchor link
Een betere database dan MySQL zou SELECT * ... GROUP BY niet eens hebben uitgevoerd, maar een foutmelding hebben gegeven vanwege de reden die Ward eerder noemde. SELECT DISTINCT wordt vaker misbruikt als snelle fix, maar verbloemt de echte oorzaak van het probleem.

Je hebt wel een FULLTEXT index op de kolom `naam` gezet, maar je gebruikt hem niet.
Om een FULLTEXT index te gebruiken moet je de MATCH AGAINST syntax gebruiken, zie: https://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html
Wat er nu gebeurt is dat MySQL voor de GROUP BY eerst alle rijen langs moet, en alle waarden van `naam` vergelijkt met wat je achter LIKE hebt staan, zonder index op de kolom `naam`. Dat duurt lang. Daarna kunnen pas de eerste 12 resultaten worden getoond.
SELECT DISTINCT is sneller, maar nog steeds niet de oplossing.
Je begint dus met het verwijderen van de FULLTEXT index. En eventueel andere ongebruikte indexes, want een tabel bijhouden kost minder resources als er geen ongebruikte indexes op zitten.

Om de SQL optimizer te helpen kan het geen kwaad om de condities in het WHERE statement te ordenen op kardinaliteit, de hoogste eerst. Dus de meest specifieke vergelijking eerst. Gesteld dat het nu al zo is, maak je een index op de drie kolommen (`cat_id`, `winkel_id`, `naam`), zie https://dev.mysql.com/doc/refman/5.6/en/create-index.html .

De uiteindelijke query presteert goed dankzij de nieuwe index:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
  SELECT `pid`, `cat_id`
  FROM `pro_pr_spec`
  WHERE `cat_id` != 0
    AND `winkel_id` = 92
    AND `naam` LIKE 'samsung%'
  GROUP BY `pid`, `cat_id`
  LIMIT 0, 12


Het datatype van de kolom `cat_id` is waarschijnlijk een integer. Het is dan niet nodig om MySQL een implicit cast te laten doen op de string '0' om die te vergelijken met het getal 0. Ook is het niet nodig om de waarde uit de kolom `winkel_id` op te vragen als je al weet. Dat scheelt I/O, vooral bij grotere resultaatsets.
Gewijzigd op 16/07/2020 15:00:43 door Ad Fundum
 



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.