MySQL Performance: MySQL => Need to optimize mysql query

Need to optimize mysql query [message #3663]

Tue, 28 October 2008 05:16

vanuatu
Messages: 5
Registered: October 2008
Location: n/a

Junior Member

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

Attachment: explain.txt
(Size: 2.05KB, Downloaded 56 time(s))

[Updated on: Tue, 28 October 2008 05:45]

Report message to a moderator