List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 1 1999 10:48am
Subject:Re: Sorting large numbers of records
View as plain text  
>>>>> "Scott" == Scott Hess <scott@stripped> writes:

Scott> Kelly Yancey <kbyanc@stripped> wrote:
>> The reason it works is because of the way LIMIT affects the returning of
>> rows, I'll see if I can summarize:
>> 
>> By using the INDEX(Approved, Name), mysql maintains an index sorted by
>> Approved then Name (for example, all the rows with Approved = 1 and Names
>> starting with A would come before all the rows with Approved = 0 and Names
>> starting with A)
>> MySQL can then perform queries which only return rows with a given value
Scott> in
>> the Approved column very quickly.
>> Then, because of the LIMIT clause, mysql scans the results to find the
>> requested subset of the results and returns them *IN ORDER* (remember the
>> key was already in sorted order by Approved *AND* Name)
>> So, since the index is actually sorted on both Approved AND Name
>> (multi-column index), the 11 entries will be returned in sorted order.

>> From the sounds of it, this case should be ripe for optimization.  Sorting a
Scott> sorted, or partially sorted, list of elements can usually be made
Scott> significantly faster than sorted unordered data.  In this case, MYSQL could
Scott> conceivably be able to "know" that the data are sorted, or mostly sorted,
Scott> and thus be able to optimize the re-sorting...

Yes, it should; The problem was that we didn't have time to fix this
optimization just now for Kelly and we come up with the above 'hack'
to temporary solve his problems

MySQL does already a lot of optimzation on ORDER BY, but it doesn't
yet handle the case of sorting on a sub field when the prefix is constant.

Thinking about this; Kelly, could you try the following query:

SELECT * from businesses WHERE Approved = 1 ORDER BY Approved,Name LIMIT 11;

MySQL should be able to use the index for the above!

Regards,
Monty
Thread
Sorting large numbers of recordsKelly Yancey27 Aug
  • Re: Sorting large numbers of recordsMartin Ramsch29 Aug
  • Sorting large numbers of recordsMichael Widenius29 Aug
    • RE: Sorting large numbers of recordsKelly Yancey31 Aug
      • Re: Sorting large numbers of recordsJames Manning31 Aug
        • RE: Sorting large numbers of recordsKelly Yancey1 Sep
          • Re: Sorting large numbers of recordsScott Hess1 Sep
            • Re: Sorting large numbers of recordsMichael Widenius1 Sep