Jump to content

How can I speed up this query?


Garethp

Recommended Posts

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');

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.