MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Scott Hess Date:August 31 1999 8:45pm
Subject:Re: Sorting large numbers of records
View as plain text  
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
> 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
sorted, or partially sorted, list of elements can usually be made
significantly faster than sorted unordered data.  In this case, MYSQL could
conceivably be able to "know" that the data are sorted, or mostly sorted,
and thus be able to optimize the re-sorting...


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