MySQL ORDER BY DESC is fast but ASC is very slow

Question

Hello, I'm completely stumped on this one. For some reason when I sort this query by DESC it's super fast, but if sorted by ASC it's extremely slow.

This takes about 150 milliseconds:

SELECT posts.id
FROM posts USE INDEX (published)
WHERE posts.feed_id IN ( 4953,622,1,1852,4952,76,623,624,10 )
ORDER BY posts.published DESC
LIMIT 0, 50;

This takes about 32 seconds:

SELECT posts.id
FROM posts USE INDEX (published)
WHERE posts.feed_id IN ( 4953,622,1,1852,4952,76,623,624,10 )
ORDER BY posts.published ASC
LIMIT 0, 50;

The EXPLAIN is the same for both queries.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  posts   index   NULL    published   5   NULL    50  Using where

I've tracked it down to "USE INDEX (published)". If I take that out it's the same performance both ways. But the EXPLAIN shows the query is less efficient overall.

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  posts   range   feed_id feed_id 4   \N  759 Using where; Using filesort

And here's the table.

CREATE TABLE `posts` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `feed_id` int(11) NOT NULL,
  `post_url` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` blob,
  `author` varchar(255) DEFAULT NULL,
  `published` int(12) DEFAULT NULL,
  `updated` datetime NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `post_url` (`post_url`,`feed_id`),
  KEY `feed_id` (`feed_id`),
  KEY `published` (`published`)
) ENGINE=InnoDB AUTO_INCREMENT=196530 DEFAULT CHARSET=latin1;

Is there a fix for this? Thanks!

Answer 1

Your index is sorted desc so when you ask for ascending it needs to do a lot more work to bring it back in that order

Answer 2

how about flipping the WHERE condition?

Answer 3

You want to add an index across (feed_id, published):

ALTER TABLE posts ADD INDEX (feed_id, published)

That'll make this query run best, and you won't need to force a particular index with USE INDEX.

Answer 4

I wouldn't suggest you create another index on the table; every time a row is inserted or deleted, each index on the table needs to be updated, slowing down INSERT queries.

The index is definitely what's slowing it down. Maybe you could try IGNORE-ing it:

SELECT posts.id
FROM posts IGNORE INDEX (published)
WHERE posts.feed_id IN ( 4953,622,1,1852,4952,76,623,624,10 )
ORDER BY posts.published ASC
LIMIT 0, 50;

Or, since the field is already KEYed, you might try the following:

SELECT posts.id
FROM posts USE KEY (published)
WHERE posts.feed_id IN ( 4953,622,1,1852,4952,76,623,624,10 )
ORDER BY posts.published ASC
LIMIT 0, 50;

Answer 5

You could get your data set first, and then order it.

Something like

SELECT posts.id FROM (
SELECT posts.id
FROM posts USE INDEX (published)
WHERE posts.feed_id IN ( 4953,622,1,1852,4952,76,623,624,10 )
LIMIT 0, 50
)
order by postS.id ASC;

It should first use the index to find all records that satisfy your "where" statement, and the will order them. But the order would be performed in a smaller set. Give it a try and then tell us.

Best Regards.