EXPLAIN EXTENDED

From Stack Overflow:

I have a table with roughly 100,000 blog postings, linked to a table with 50 feeds via an 1:n relationship.

When I query both tables with a SELECT statement, ordered by a datetime field of the postings table, MySQL always uses filesort, resulting in very slow query times (more than 1 second).

SELECT
`postings`.`id`,
UNIX_TIMESTAMP(postings.post_date) as post_date,
`postings`.`link`,
`postings`.`title`,
`postings`.`author`,
`postings`.`excerpt`,
`postings`.`long_excerpt`,
`feeds`.`title` AS feed_title,
`feeds`.`website` AS feed_website
FROM
`postings`
JOIN
`feeds`
ON
`feeds`.`id` = `postings`.`feed_id`
WHERE
`feeds`.`type` = 1 AND
`postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

This is a nice question that illustrates how to choose which indexes to create.

At first blush it seems to be a job for a composite index on (is_active, user_offtopic_count, post_date). Really, we could use the first two fields to filter on and the third field to order by.

But the problem here is that the query uses a range condition on postings.user_offtopic_count. In this case, the index cannot be used for ordering.
Read the rest of this entry »