Need to optimize mysql query

MySQL & MariaDB Other MySQL® Questions
1

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

2

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/

3

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

4

What happens if you remove STRAIGHT_JOIN?

Whats the value of $limit?

5

Hi,

When I remove STRAIGHT_JOIN the query takes like 10secs.

Limit = 5
Offset value is empty

6

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.

7

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 $?