Hi all,
I want to show a top 5 of products from my product table (babyfoontest) on the results page and show a price comparison with those 5 products. All prices are in a seperate table named “prijzen” with a shopname and deeplink. I join those tables and use a GROUP_CONCAT to match all prices, based on EAN. I set the limit to 5, because I only need 5 products for my result page.
This is my SQL:
[code]SELECT babyfoontest.idnummer, babyfoontest.typenummer, deeplink, merk, afbeelding, GROUP_CONCAT(price) as prijs, GROUP_CONCAT(producturl) as deeplink, $query_string_variable AS total
FROM prijzen
INNER JOIN babyfoontest
ON prijzen.eancode = babyfoontest.ean
WHERE
(shopnaam = ‘amazon’ AND categorie = ‘Babby en dreumes’)
GROUP BY eancode
ORDER BY total DESC, ABS(prijs)
LIMIT 0, 5;[/code]
The loading time is about 12 seconds now. If I remove the LIMIT, the loading time is still 12 seconds so I think my statement try to match all products instead of 5. Do I need a subquery for this? How can I improve my loading speed? I only need to concate and join AFTER the 5 products are selected?
Thanks for helping me out!!
Kind regards,
Mark