On Mon, Feb 19, 2001 at 10:22:58PM -0500, Paradox wrote:
> I have a table with an integer auto increment primary key, INVNUM, and a
> CHAR(48) field NAME.
>
> Is there a way I can have my table presorted by the text field,
Yes, in 3.23.33 you can use myisamchk with the -R option. I don't have
a clue what version and table types you're using, so this may not work
for you. Type myisamchk --help for a description of its parameters.
> so that a
> select call with "ORDER BY NAME" is just as fast as "ORDER BY INVNUM"? I
> have tried indexing the textfield, both a few leading characters and the
> entire field, but both have no effect. The text field is not unique. The
> difference in time between these two queries is about 3 seconds:
>
> SELECT INVNUM FROM INVOICES ORDER BY INVNUM;
>
> SELECT INVNUM FROM INVOICES ORDER BY NAME;
>
Well then I don't think it will do you any good. MySQL seems to decide
to NOT use the index. This is a good decision since you are querying
for all records and the field you want (INVNUM) is not part of the
index it could use for ORDER BY (i.e. NAME).
I don't see a way to improve this. An index on (NAME,INVNUM) might
help though if your records are large and you can get MySQL to decide
to use the index. Check the section on query optimization and the
description for the EXPLAIN command for this.
Regards,
Fred.
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
