How can I optimize this confusing slow query in MySQL?

Question

I have a table of blog posts, each with a foreign key is returned to it by the author. There are <15,000 entries in this table. This query scans over 19,000 rows (per EXPLAIN ), requires file management (which may be normal MySQL behavior), and takes more than 400 ms to return 5 rows. possibly due to the complex WHERE used to check if the item is actually published.

Expensive stack overflow, how can I deal with this request under control?

Note: although this criterion can be simplified, all conditions are required.

 SELECT `blog_post.id`, `blog_post.title`, `blog_post.author_id`, `blog_post.has_been_fact_checked`, `blog_post.published_date`, `blog_post.ordering`, `auth_user.username`, `auth_user.email` FROM `blog_post` INNER JOIN `auth_user` ON (`blog_post`.`author_id` = `auth_user`.`id`) WHERE (`blog_post`.`is_approved` = True AND `blog_post`.`has_been_fact_checked` = True AND `blog_post`.`published_date` IS NOT NULL AND `blog_post`.`published_date` <= '2010-10-25 22:40:05' ) ORDER BY `blog_post`.`published_date` DESC, `blog_post`.`ordering` ASC, `blog_post`.`id` DESC LIMIT 5 

Besides PK, I have the following indexes in the table:

 idx_published_blog_post -> blog_post(is_approved, has_been_fact_checked, published_date) idx_pub_date -> blog_post(published_date) 

Exiting EXPLAIN as follows:

 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: blog_post type: ref possible_keys: blog_post_author_id,idx_published_blog_post,idx_pub_date key: idx_published_blog_post key_len: 4 ref: const,const rows: 19856 Extra: Using where; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: auth_user type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: blog.blog_post.author_id rows: 1 Extra: Using index 2 rows in set (0.00 sec) 

Side note: 2010-10-25 22:40:05 - this is just the date generated by the code that executes this request.

Thanks so much for any help!

Answer 1

MySQL does not support ASC/DESC clauses in indexes.

You will need to create a separate column called reverse_ordering and set its value to -ordering (assuming ordering is a numeric value)

Then you can create the following index:

 CREATE INDEX ix_blogpost_a_c_p_ro_id ON blog_post (is_approved, has_been_fact_checked, published_date, reverse_ordering, id) 

and rewrite your request:

 SELECT `blog_post.id`, `blog_post.title`, `blog_post.author_id`, `blog_post.has_been_fact_checked`, `blog_post.published_date`, `blog_post.ordering`, `auth_user.username`, `auth_user.email` FROM `blog_post` INNER JOIN `auth_user` ON `blog_post`.`author_id` = `auth_user`.`id` WHERE `blog_post`.`is_approved` = 1 AND `blog_post`.`has_been_fact_checked` = 1 AND `blog_post`.`published_date` <= '2010-10-25 22:40:05' ORDER BY `blog_post`.`published_date` DESC, `blog_post`.`reverse_ordering` DESC, `blog_post`.`id` DESC LIMIT 5 

You can get rid of the IS NULL check, because by virtue of the inequality condition.

Update:

You can also read this article:

• Mixed ASC / DESC sorting in MySQL

Answer 2

Make it look like "blog_post" with all the conditions that you applied in the request (where clause), and connect to auth_user directly with this view.

feel free to ask if not clear. :)

Answer 3

It seems to me that the file center can kill speed. If you can get the ORDER BY fields in the index that is used, you can get an increase in speed. Try changing:

idx_published_blog_post → blog_post (is_approved, has_been_fact_checked, published_date)

to

idx_published_blog_post → blog_post (is_approved, has_been_fact_checked, published_date DESC, order ASC, id DESC)

A few thoughts: under what circumstances do you have null published_date, can a date range search be faster? Also, publish_date seems to be a date time field, so when sorting, do you really have messages that can post every second so you need other sort fields?