Hi,
This select takes more than 4 secs to execute. Needs to be much faster. I am kind of stuck…
Could anyone help me out?
$query = "
SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS
p.id AS id
,p.rating
,DATE_FORMAT(p.added, ‘" . DB_DATE_FORMAT . "’) AS added
,p.family
,p.designer_id
,p.vendor_id
,p.user_id
,p.name
,p.os
,p.type
,p.license
,p.image_name
,p.meta
,cp.category_id AS category_id
,c.parent_id AS parent_id
,ac.name AS parent_name
,c.name AS category_name
,COUNT(co.id) AS comments
,d.display AS designer_name
,v.display AS vendor_name
FROM products AS p
JOIN (categories_products AS cp, categories AS c, categories AS ac)
ON (cp.product_id = p.id AND c.id = cp.category_id AND ac.id = c.parent_id)
LEFT JOIN (comments AS co, designers AS d, vendors AS v)
ON (co.id = p.id OR d.id = p.designer_id OR v.id = p.vendor_id)
GROUP BY id
ORDER BY added DESC
$limit
$offset
";
Explain attached.
Thanks
Paul