I've got a table of blogs, each having a foreign key to it's author. You will find < 15,000 records within this table. This question scans over 19,000 rows (per EXPLAIN), takes a filesort (that could be regular MySQL behavior), and gets control 400ms to come back 5 rows. possibly due to the complicated WHERE used to see if the product is really released.

Dearest Stack Overflow, how do i wrangle this question in check?

Note: although this criteria may be up for simplification, all the the weather is needed.

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 the PKs, I've the next indexes up for grabs:

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

The output from EXPLAIN appears like this:

*************************** 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 is simply a date produced through the code that executes this question.

Thanks a lot for just about any &lifier all help!

MySQL doesn't support ASC/DESC clauses in indexes.

You should produce a separate column known as reverse_ordering and hang its value to -ordering (so long as ordering is definitely an number value)

Then you may produce 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 query:

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 might eliminate IS NULL check, because the inequality condition suggests it.

Update:

Additionally you may want to look at this article:

Create a look at "blog_publish" with all of conditions you've used in query (where clause) and perform the joining of "auth_user" direct with this particular view.

take a moment request otherwise obvious. :)