Welcome to WebmasterWorld Guest from 100.26.182.28

Forum Moderators: open

Message Too Old, No Replies

Ordering Rows Using Index

     
11:50 am on May 10, 2007 (gmt 0)

New User

10+ Year Member

joined:Mar 12, 2007
posts:26
votes: 0


Is there a way to tell MySQL to order rows using a certain index?

I'm running the following query on a table with an index on dateLast, and one on status:

SELECT field FROM table
WHERE (DATEDIFF(NOW(), dateLast) > 7) AND (status = 1)
ORDER BY dateLast ASC
LIMIT 10

This query takes about 80 seconds, because MySQL uses the index on 'status', which means it has to sort the rows using filesort.

When I remove 'status' from the query:

SELECT field FROM table
WHERE (DATEDIFF(NOW(), dateLast) > 7)
ORDER BY dateLast ASC
LIMIT 10

The query doesn't even take a second, because MySQL can use the index on dateLast to both check the where condition and sort the rows.

Any ideas?

12:40 pm on May 10, 2007 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11822
votes: 236


instead if 2 indices try using a multiple-column index on dateLast, status.

CREATE INDEX date_status ON table (dateLast, status);
DROP INDEX dateLast ON table;
DROP INDEX status ON table;
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members