在postings (is_active, post_date)上(按此顺序)创建一个复合索引.

Create a composite index either on postings (is_active, post_date) (in that order).

它将用于在is_active上进行过滤和按post_date进行排序.

It will be used both for filtering on is_active and ordering by post_date.

MySQL应该在EXPLAIN EXTENDED中的该索引上显示REF访问方法.

MySQL should show REF access method over this index in EXPLAIN EXTENDED.

请注意，您在user_offtopic_count上具有RANGE过滤条件，这就是为什么在过滤和按其他字段排序时都不能在该字段上使用索引的原因.

Note that you have a RANGE filtering condition over user_offtopic_count, that's why you cannot use an index over this field both in filtering and in sorting by other field.

根据您的user_offtopic_count的选择性(即，有多少行满足user_offtopic_count < 10)，在user_offtopic_count上创建索引并对post_dates进行排序可能会更有用.

Depending on how selective is your user_offtopic_count (i. e. how many rows satisfy user_offtopic_count < 10), it may be more useful to create an index on user_offtopic_count and let the post_dates be sorted.

为此，请在postings (is_active, user_offtopic_count)上创建一个复合索引，并确保使用对该索引的RANGE访问方法.

To do this, create a composite index on postings (is_active, user_offtopic_count) and make sure the RANGE access method over this index is used.

哪个索引会更快取决于您的数据分布.创建两个索引，FORCE它们，看看哪个更快:

Which index will be faster depends on your data distribuion. Create both indexes, FORCE them and see which is faster:

CREATE INDEX ix_active_offtopic ON postings (is_active, user_offtopic_count);
CREATE INDEX ix_active_date ON postings (is_active, post_date);

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` FORCE INDEX (ix_active_offtopic)
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 should show RANGE access with few rows and keep the FILESORT */

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` FORCE INDEX (ix_active_date)
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 should show REF access with lots of rows and no FILESORT */