MySQL Performance: MySQL => Need to optimize mysql query

Members

Help

Home

Home » Performance » MySQL » Need to optimize mysql query

Show: Today's Messages :: Show Polls :: Message Navigator

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 45 time(s))

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

Report message to a moderator

Re: Need to optimize mysql query [message #3665 is a reply to message #3663 ]

Tue, 28 October 2008 13:59

razdaman
Messages: 26
Registered: May 2007

Junior Member

Is it any faster if you run it without SQL_CALC_FOUND_ROWS?

If yes - you would probably find a solution to your problem here:
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_f ound_rows-or-not-to-sql_calc_found_rows/

Report message to a moderator

Re: Need to optimize mysql query [message #3667 is a reply to message #3663 ]

Tue, 28 October 2008 14:59

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

Junior Member

Hi,

It's better 3.36sec vs 4.07

I would be great if I could drop temporary/filesort use somehow...

Any help appreciated.

Paul

Report message to a moderator

Re: Need to optimize mysql query [message #3668 is a reply to message #3663 ]

Tue, 28 October 2008 15:45

razdaman
Messages: 26
Registered: May 2007

Junior Member

What happens if you remove STRAIGHT_JOIN?

Whats the value of $limit?

[Updated on: Tue, 28 October 2008 15:49]

Report message to a moderator

Re: Need to optimize mysql query [message #3669 is a reply to message #3668 ]

Tue, 28 October 2008 15:55

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

Junior Member

Hi,

When I remove STRAIGHT_JOIN the query takes like 10secs.

Limit = 5
Offset value is empty

Report message to a moderator

Re: Need to optimize mysql query [message #3671 is a reply to message #3663 ]

Tue, 28 October 2008 16:12

razdaman
Messages: 26
Registered: May 2007

Junior Member

I'm afraid it will not be easy to optimize it further. You will have to restructure your query.

The reason why the query is slow is that your many joins results in a result set with around 21000 rows (according to the explain). This alone takes time. Make sure you have indexes on all foreign keys used to accomplish the join - this might make the original query faster.

After this mysql must do a group by id on this result set - also a rather expensive task (when performed on that many rows). And last it does a filesort on the grouped result set.

I would suggest you try to break of the query in smaller ones (each query should touch fewer tables if possible). This would make it easier to take advantage of indexes.

Report message to a moderator

Re: Need to optimize mysql query [message #3673 is a reply to message #3671 ]

Tue, 28 October 2008 16:33

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

Junior Member

categories_products has like 41k results already...each product can be assigned to multiple categories..so it's likely this table will grow faster than the actual products table.

I know.....already playing with it like the 3rd day.

I don't think I'm experienced enough to do that myself.

Know anyone who would be willing to help with it for some $?

Report message to a moderator