Try giving a range in the where clause, I have the latest source (from bit keeper and it seems to alway use the index here) there is lots of good things coming in 4.0.1.
Here I've provide some examples from my test system. Using a date field. Forcing both a sorted response and an unsorted response. I think there will be improvements in 4.0.1, and more in 4.0.2! Best of luck again, I am goin home soon! Ken Here are my examples (this is a fairly slow old system): mysql> select count(*) from sali; +----------+ | count(*) | +----------+ | 11534 | +----------+ 1 row in set (0.02 sec) mysql> select saliDate,saliRef from sali order by saliDate desc limit 10; +---------------------+---------+ | saliDate | saliRef | +---------------------+---------+ | 2001-08-07 15:22:19 | 1779 | | 2001-08-07 13:04:18 | 1 | | 2001-06-22 10:01:55 | 1777 | | 2001-06-22 09:58:30 | 1776 | | 2001-06-22 09:55:36 | 1775 | | 2001-06-19 13:33:41 | 1 | | 2001-06-09 11:07:38 | 3 | | 2001-06-09 11:07:38 | 3 | | 2001-06-09 11:07:38 | 3 | | 2001-05-29 19:32:43 | 52 | +---------------------+---------+ 10 rows in set (0.02 sec) mysql> explain select saliDate,saliRef from sali order by saliDate desc limit 10; +-------+-------+---------------+----------+---------+------+-------+- ------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+----------+---------+------+-------+- ------+ | sali | index | NULL | saliDate | 10 | NULL | 11534 | | +-------+-------+---------------+----------+---------+------+-------+- ------+ 1 row in set (0.02 sec) Another explain (without saliRef in the response): mysql> explain select saliDate from sali order by saliDate desc limit 10; +-------+-------+---------------+----------+---------+------+-------+- ------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+----------+---------+------+-------+- ------------+ | sali | index | NULL | saliDate | 10 | NULL | 11534 | Using index | +-------+-------+---------------+----------+---------+------+-------+- ------------+ 1 row in set (0.02 sec) mysql> And here I force it to sort, by not using an index in the order by: mysql> select saliDate,saliRef from sali order by saliRef desc limit 10; +---------------------+---------+ | saliDate | saliRef | +---------------------+---------+ | 2001-08-07 15:22:19 | 1779 | | 2001-06-22 10:01:55 | 1777 | | 2001-06-22 09:58:30 | 1776 | | 2001-06-22 09:55:36 | 1775 | | 2001-05-29 19:14:29 | 1774 | | 2001-05-29 18:02:58 | 1773 | | 2001-05-29 16:23:08 | 1772 | | 2001-05-29 14:51:48 | 1771 | | 2001-05-29 14:45:16 | 1770 | | 2001-05-29 14:44:27 | 1769 | +---------------------+---------+ 10 rows in set (0.23 sec) +-------+-------+---------------+---------+---------+------+-------+-- ---------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+-------+---------------+---------+---------+------+-------+-- ---------------------------+ | sali | index | NULL | PRIMARY | 20 | NULL | 11534 | Using index; Using filesort | +-------+-------+---------------+---------+---------+------+-------+-- ---------------------------+ 1 row in set (0.01 sec) mys ----- Original Message ----- From: "Franklin Schmidt" <[EMAIL PROTECTED]> To: "Ken Menzel" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, November 30, 2001 7:50 PM Subject: Re: descending keys > Ken Menzel wrote: > > > > Actually it MySQL 4 does. There is a small bug in it right now where > > it won't use the descending index witout a range in the where clause, > > but this is supposed to be fixed in 4.0.2. > > Thanks, I am using MySQL 4. I actually want this for "order by". When > I do "create index idx on t (i1, i2 desc, i3)" and then do "show index > from t", I only see "A" in the Collation column. When I do "explain > select * from t order by i1, i2 desc, i3", I see that it does a > filesort. > --------------------------------------------------------------------- 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
