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.
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:
SELECT *
FROM pro_pr_spec
WHERE cat_id IS NOT NULL
AND winkel_id = 92
AND naam LIKE 'samsung%'
LIMIT 0, 12
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.
[size=xsmall]Toevoeging op 15/07/2020 08:19:33:[/size]
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?
[size=xsmall]Toevoeging op 15/07/2020 08:40:22:[/size]
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
". 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
?Onbekende gebruiker
16-07-2020 14:54
gewijzigd op 16-07-2020 15:00
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:
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.
