MySQL Forums
Forum List  »  Performance

Re: Using 'Order By' disables use of index
Posted by: Jason Winnebeck
Date: January 10, 2005 08:09AM

Harrison Fisk wrote:
> This isn't a bug. There are times that it is
> actually slower to use an index than to not use
> the index. ... 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.

Hmm that sounds a little unintuitive to me that reading the whole file then rewriting it again to sort it on disk is faster than all of the seeks, espically when you consider the number of seeks to sort the data on disk. It sounds plausible though if you consider a large sort_buffer_size, but as you say that is mostly irrelevant for my situation.

I think that the manual should be updated then, as the manual clearly states in section 7.2.10 that the index is used for "SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;" and that if explain shows "using filesort", then it implies that something is wrong. It would be nice if the manual mentions what you've told me.

Out of curiousity, if you use alter table to sort the data file on disk, does that dramatically decrease the time to do the filesort?

> 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.

The exception to this is if you use a clustered index, as in SQL Server, correct, which can be roughly achieved in MySQL by running 'alter table x order by p' often?

> 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.

Yes, our seek time is 0.04ms, compared to something like 8-12ms, which is typical for a physical magnetic drive.

> 2. You are using mysql_use_result to stream so
> you want the first row as fast as possible.

Although streaming records linearly is beneficial so that the client can start processing the data set before it is completely downloaded, that is not required. What is required is that we use no extra memory or disk as we have none to use, and that is why we use streaming.

> 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).

Thanks. This will unfortunately take some extra logic code on our part as we have queries that use another index on that table, so we have to make sure not to use FORCE INDEX when that index is not applicable. We might have to do this anyway: we've also noticed that if we pull out records using "in": (SELECT * FROM table where key_part1 IN ( a, b, c ) and key_part2 between y and z order by key_part1, key_part2) that it does a sort (which requires memory and time), and it is faster for us and does not require the extra memory if we sort the "IN" list, break it into one query for each key_part1 and append the results of the queries.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Using 'Order By' disables use of index
3089
January 10, 2005 08:09AM


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.