Index for order by and limit

[ianevans]

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]

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]

What about an index just on date?

[ianevans]

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.