How can I speed up this query?

[Garethp]

So, I've got this project built in Zend Framework, and this page particularly is slow, due to this query

 

# Time: 120720 16:57:59
# Query_time: 10.803516  Lock_time: 0.000127 Rows_sent: 10  Rows_examined: 21057621
SET timestamp=1342767479;
SELECT `projects_projects`.*, COUNT(*) AS `Hits` FROM `projects_projects`
LEFT JOIN `projects_hits` ON projects_hits.ProjectID=projects_projects.ID
LEFT JOIN `projects_in_subcategories` ON projects_in_subcategories.ProjectID=projects_projects.ID
LEFT JOIN `projects_subcategories` ON projects_subcategories.ID=projects_in_subcategories.SubCategoryID
LEFT JOIN `projects_categories` ON projects_categories.ID=projects_subcategories.CategoryID WHERE (projects_projects.Published=1) AND (projects_categories.ID='10') GROUP BY `projects_projects`.`ID` ORDER BY `Created` DESC LIMIT 10 OFFSET 80;

 

As detected by my Slow Query Log

 

Basically what I've got is a table of projects, a table of project categories, and a table of subcategories, and a joining table that ties projects and subcategories together, as well as get how many hits said projects have

 

And this page has to show 10 projects within a certain parent category. This particular query is taking 10 seconds, as you can see, and Zend is also using another query to create the paginator count, which takes another 10 seconds (query at the bottom). So what can I do to speed this up?

 

Paginator Query

# Time: 120720 16:57:48
# User@Host: habitususer5[habitususer5] @  [113.192.3.227]
# Query_time: 10.088245  Lock_time: 0.000335 Rows_sent: 1  Rows_examined: 21057435
SET timestamp=1342767468;
SELECT COUNT(DISTINCT `projects_projects`.`ID`) AS `zend_paginator_row_count` FROM `projects_projects`
LEFT JOIN `projects_hits` ON projects_hits.ProjectID=projects_projects.ID
LEFT JOIN `projects_in_subcategories` ON projects_in_subcategories.ProjectID=projects_projects.ID
LEFT JOIN `projects_subcategories` ON projects_subcategories.ID=projects_in_subcategories.SubCategoryID
LEFT JOIN `projects_categories` ON projects_categories.ID=projects_subcategories.CategoryID WHERE (projects_projects.Published=1) AND (projects_categories.ID='10');

[Barand]

INNER JOINS are faster than LEFT JOINS, so if there will always be a match, or you only require those that have a match, use those.

[mikosiko]

before anything.... post the EXPLAIN plan for each query

[xyph]

INNER JOINs will probably help here.

 

Knowing how you've indexed your data is important as well. Anything you're using in the ON clause or the WHERE clause should be indexed, assuming you've got enough cardinality.

 

Finally, avoid quoting integers unless it's a text/char column type you're checking against. Though this doesn't really hurt performance in the WHERE clause, it can cause all sorts of trouble in ON clauses, and forcing your engine to do a conversion it doesn't have to is a complete waste of resources, no matter how little.

 

If you require an outside value to be an integer, don't escape_string() it, cast it as or verify it's an integer.

[fenway]

before anything.... post the EXPLAIN plan for each query

Yes, please!

[The Little Guy]

I have been told, if your using InnoDB, that using count(*) is VERY slow and you should replace the * with a primary key. If you are using MyISAM, it may be faster it may not IDK. I have never tested that though (on either Engines).

 

Also make sure you indexed the values on the joins.

[fenway]

I have been told, if your using InnoDB, that using count(*) is VERY slow and you should replace the * with a primary key. If you are using MyISAM, it may be faster it may not IDK. I have never tested that though (on either Engines).

 

Also make sure you indexed the values on the joins.

If you're never tested it, and you don't know, then what advice are you actually giving?

 

As long as a WHERE clause can be utilized, in most cases, InnoDB won't need to perform a full table scan, and counting should be fast.