group by in mysql op een 4.5 Miljoen rijen tabel langzaam
Ik heb dus een 4.5 Miljoen rows tabel.
Als ik dit uitvoer.
Code (php)
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
Wat voor informatie zie je dan allemaal?
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
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.
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)
1
2
3
4
5
6
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
FROM pro_pr_spec
WHERE cat_id IS NOT NULL
AND winkel_id = 92
AND naam LIKE 'samsung%'
LIMIT 0, 12
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
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
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)
1
2
3
4
5
6
7
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
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.