#64 Query in storylist.php is very inefficient

[Travis Miller]

Ordering storylists by any of the available options can produce some very long-running queries in a real-world environment. On our site (with MySQL 4.1.20, and ~130,000 stories in the database), the scores of queries running at any given time often cause MySQL to bog down completely, and require a restart of mysqld.

If you do this:

EXPLAIN SELECT * FROM props_stories WHERE section_id IN ( 1, 2, 3 ) ORDER BY published_stamp

...you'll get this:

+----+-------------+---------------+-------+---------------+------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | props_stories | range | section_id | section_id | 5 | NULL | 323 | Using where; Using filesort |
+----+-------------+---------------+-------+---------------+------------+---------+------+------+-----------------------------+

You can see that it's using filesort, which is very, very, VERY slow.

Might just need some carefully deployed indexes on the columns...