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 »