MySQL Forums
Forum List  »  Performance

Re: Using 'Order By' disables use of index
Posted by: Harrison Fisk
Date: January 08, 2005 11:38AM

Hi,

This isn't a bug. There are times that it is actually slower to use an index than to not use the index. The most common time is when the index wouldn't be very selective. To see how this works lets take a look at the above queries.

select * from DMCalcNumData order by portId, `time`;

Now if it used the index to do this what would occur:

1. It reads the entire index (7M entries).
2. Reads the entire table in a random order causing up to 7M disk seeks.

Now if it didn't use the index:

1. Read the entire table continous (1 disk seek)
2. Sort data in sort_buffer only writing to disk chunks of data the size of sort_buffer.

The second one is generally faster. The reason is that it is much faster to read and write continous chunks of data from a table than to do many disk seeks.

Now the cut off of what is selective and what isn't selective is determined on the fly.
This will explain all of the behavior you have seen:

select * from DMCalcNumData where portId > 0 and portId < 10000 order by portId, time;
MySQL thinks it will match only 1M out of the 7M rows, so it figures that it is worth it to use it.

select * from DMCalcNumData where portId > 0 and portId < 1000000 order by portId, time;
MySQL thinks it will match too many rows, so it is faster to do the second method of a full table scan followed by sorting.

select * from DMCalcNumData order by portId, `time` limit 7669089;
In this case it assumes due to the LIMIT that it won't match and return too much of the table. The reason for this is that normally LIMIT isn't used with such a large number as it has almost no effect. However, I agree that it should check to see if the LIMIT is so large and decide not to use the index. This is definitely a feature to be added.

So this is the way it normally works, faster to actually not use the index (feel free to benchmark on a normal database server (not the embedded case that you have) if you don't believe me). All RDBMS will do the same thing by default.

However in your case it might be a bit different for a few reasons.

1. You are using a flash drive, the seek time most likely isn't that big of a factor here.
2. You are using mysql_use_result to stream so you want the first row as fast as possible.

Because of this you will want to override the behavior and use a FORCE INDEX (PRIMARY). That will cause it always use the index as you have seen. That should work in the most recent 4.0 and 4.1. There was a bug in early 4.0 where it would still use a USING FILESORT (as you noted). The bug was fixed in 4.0.19 (see http://dev.mysql.com/doc/mysql/en/News-4.0.19.html for details).

I hope that helps to explain why the current behavior is correct. Let me know if you have any questions about what I have said.

Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Using 'Order By' disables use of index
5083
January 08, 2005 11:38AM


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.