MySQL Forums
Forum List  »  Performance

Index usage for ORDER BY / LIMIT?
Posted by: Boris Burtin
Date: June 14, 2005 11:34AM

I'd like to get the latest 30 dates from a table that could potentially have hundreds of thousands of rows. I thought that the index on the date column would provide enough information to allow MySQL to quickly get the data. But the way I read the EXPLAIN plan, MySQL is doing a table scan (rows=5415).

Am I misreading the EXPLAIN plan? Is there a way to tell whether MySQL is scanning just the index or the actual table? I'm hoping to avoid adding a derived column just to get this query to run optimally.

I'd appreciate any help I can get.

Thanks,

Boris

---------------

mysql> EXPLAIN SELECT date FROM my_table ORDER BY date LIMIT 30 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: mail_item
type: index
possible_keys: NULL
key: i_date
key_len: 8
ref: NULL
rows: 5415
Extra: Using index
1 row in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Index usage for ORDER BY / LIMIT?
2152
June 14, 2005 11:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.