Index for order by and limit

Forum Moderators: open

Message Too Old, No Replies

Index for order by and limit

ianevans

1:37 am on Jul 21, 2008 (gmt 0)

The SELECT:

SELECT article_id, slug, date, headline, description FROM news ORDER BY date DESC, article_id DESC LIMIT 4;

The intention:

To give me the four most recent news articles ordered by date and the order in which they were entered.

The quandary:

Mysql is looking up 1108 rows to do this. I thought doing a multi-column index on (date,article_id) would help it but it still shows in the slow log.

Any way to speed this up?

maximillianos

12:58 pm on Jul 21, 2008 (gmt 0)

Interesting. I would have thought it would not need to look at them all. Must be because you have no where condition.

A thousand rows is really not a big deal performance wise, now when you get to millions, that can hurt... ;-)

If it is really a problem, maybe just use a second table to store the most recent articles (kind of a like a cache table of the top 4 articles).

LifeinAsia

4:47 pm on Jul 21, 2008 (gmt 0)

What about an index just on date?

ianevans

5:21 pm on Jul 21, 2008 (gmt 0)

Well I took the suggestion of adding a WHERE. Made it so it looked for stories with a timestamp of thirty days or less from the current date and the number of returned rows dropped enormously.

Thanks.