List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:August 29 1999 6:34pm
Subject:Sorting large numbers of records
View as plain text  
>>>>> "Kelly" == Kelly Yancey <kbyanc@stripped> writes:

Kelly>   I have a table with approximately 20,000 variable-sized records that looks
Kelly> like this:

<cut>

Hi!

Could you please post the output from:

SHOW KEYS from Approved;

or 

mysqldump --no-data 'database' Approved.

I would like to know if you have an index where 'Name' is the first
part.


<cut>

Kelly>   On further reflection, I realized that it would be *really* impressive for
Kelly> mysql to give any better results...simply because of what I am asking it to
Kelly> do: find all the results which match X, sort them, and then give me this
Kelly> slice of them. So my question is not how to make the query faster. I doubt
Kelly> the query could be much faster (someone might suggest converting all the
Kelly> VARCHAR fields to CHAR, but I tried that....the query took almost half as
Kelly> long, but the database was 15x as large; even at .5 seconds the query was
Kelly> taking too long at at 90Meg, that was a large price to pay).

<cut>

In theory, you can make your query VERY fast with an index on:

INDEX (Approved,name)

To make this work, one would however add an optimization for this case 
to the

sql/sql_select.cc::create_sort_index

function to use 'read_next' in this case.

It's by the way possible that if you add the above index and remove
the ORDER BY part, that you will get your result sorted!
(This is because the range optimizer will use the index on Approved if 
you use LIMIT)

If this doesn't help, then a index on only 'NAME' may help.

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