SQL Query Optimization

Question

I am trying to speed up this django app (note: I didn't design this... just stuck maintaining it) and the biggest bottle neck seems to be these queries that are being generated by the admin. We have a content class that 4-5 other sub-classes inherit from and anytime the master list is pulled up in the admin a query like this is generated:

SELECT `content_content`.`id`,
       `content_content`.`issue_id`,
       `content_content`.`slug`,
       `content_content`.`section_id`,
       `content_content`.`priority`,
       `content_content`.`group_id`,
       `content_content`.`rotatable`,
       `content_content`.`pub_status`,
       `content_content`.`created_on`,
       `content_content`.`modified_on`,
       `content_content`.`old_pk`,
       `content_content`.`content_type_id`,
       `content_image`.`content_ptr_id`,
       `content_image`.`caption`,
       `content_image`.`kicker`,
       `content_image`.`pic`,
       `content_image`.`crop_x`,
       `content_image`.`crop_y`,
       `content_image`.`crop_side`,
       `content_issue`.`id`,
       `content_issue`.`special_issue_name`,
       `content_issue`.`web_publish_date`,
       `content_issue`.`issue_date`,
       `content_issue`.`fm_name`,
       `content_issue`.`arts_name`,
       `content_issue`.`comments`,
       `content_section`.`id`,
       `content_section`.`name`,
       `content_section`.`audiodizer_id`
  FROM `content_image`
 INNER
  JOIN `content_content`
    ON `content_image`.`content_ptr_id` = `content_content`.`id`
 INNER
  JOIN `content_issue`
    ON `content_content`.`issue_id` = `content_issue`.`id`
 INNER
  JOIN `content_section`
    ON `content_content`.`section_id` = `content_section`.`id`
 WHERE NOT ( `content_content`.`pub_status` = -1 )
 ORDER BY `content_issue`.`issue_date` DESC LIMIT 30

I ran an EXPLAIN on this and got the following:

+----+-------------+-----------------+--------+-------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------+-------+---------------------------------+
| id | select_type | table           | type   | possible_keys                                                                                   | key     | key_len | ref                                  | rows  | Extra                           |
+----+-------------+-----------------+--------+-------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | content_image   | ALL    | PRIMARY                                                                                         | NULL    | NULL    | NULL                                 | 40499 | Using temporary; Using filesort |
|  1 | SIMPLE      | content_content | eq_ref | PRIMARY,issue_id,content_content_issue_id,content_content_section_id,content_content_pub_status | PRIMARY | 4       | content_image.content_ptr_id         |     1 | Using where                     |
|  1 | SIMPLE      | content_section | eq_ref | PRIMARY                                                                                         | PRIMARY | 4       | content_content.section_id           |     1 |                                 |
|  1 | SIMPLE      | content_issue   | eq_ref | PRIMARY                                                                                         | PRIMARY | 4       | content_content.issue_id             |     1 |                                 |
+----+-------------+-----------------+--------+-------------------------------------------------------------------------------------------------+---------+---------+--------------------------------------+-------+---------------------------------+

Now, from what I've read, I need to somehow figure out how to make the query to content_image not be terrible; however, I'm drawing a blank on where to start.

You select everything from content_image table (since you don't have any WHERE that filters out some rows) - so you have a table fullscan. What else do you expect to see? — zerkms
– zerkms, Commented Jan 24, 2012 at 3:03

ruakh · Accepted Answer · 2012-01-24 03:17:46Z

Currently, judging by the execution plan, MySQL is starting with content_image, retrieving all rows, and only thereafter using primary keys on the other tables: content_image has a foreign key to content_content, and content_content has foreign keys to content_issue and content_section. Also, only after all the joins are complete can it make much use of the ORDER BY content_issue.issue_date DESC LIMIT 30, since it can't tell which of these joins might fail, and therefore, how many records from content_issue will really be needed before it can get the first thirty rows of output.

So, I would try the following:

Change JOIN content_issue to JOIN (SELECT * FROM content_issue ORDER BY issue_date DESC LIMIT 30) content_issue. This will allow MySQL, if it starts with content_issue and works its way to the other tables, to grab a very small subset of content_issue.
- Note: properly speaking, this changes the semantics of the query: it means that only records from at most the last 30 content_issues will be retrieved, and therefore that if some of those issues don't have published contents with images, then fewer than 30 records will be retrieved. I don't have enough information about your data to gauge whether this change of semantics would actually change the results you get.
- Also note: I'm not suggesting to remove the ORDER BY content_issue.issue_date DESC LIMIT 30 from the end of the query. I think you want it in both places.
Add an index on content_issue.issue_date, to optimize the above subquery.
Add an index on content_image.content_ptr_id, so MySQL can work its way from content_content to content_image without doing a full table scan.

Collectives™ on Stack Overflow

SQL Query Optimization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related