>>>>> "SM" == Sinisa Milivojevic <[EMAIL PROTECTED]>
>>>>> wrote the following on Sun, 3 Jun 2001 14:52:44 +0300 (EEST)
>> Hi, I think I found a bug in mysql. I tried the mysqlbug thing
>> but it didn't work right away. I hope this message will be
>> detailed enough. I am not subscribed to the mailing list.
>> mysql -e "EXPLAIN SELECT * FROM sqmail.sequence_data WHERE sid =
>> 16 ORDER BY sid desc,id desc LIMIT 10"
>> The output of the last command contains "Using filesort" when it
>> seems like it shouldn't.
SM> MySQL is still using filesort, because of "ORDER BY sid desc,id
SM> desc".
SM> Simply, setting DESC disables usage of index ...
Thank you for the response. In which cases does DESC disable indexed
sorting? Perhaps there is a a way around this for the time being?
For instance, I noticed that although:
SELECT * FROM sqmail.sequence_data WHERE sid = 16 ORDER BY sid DESC,id
DESC LIMIT 10
uses filesort,
SELECT * FROM sqmail.sequence_data WHERE id < 100000 ORDER BY
sid DESC,id DESC LIMIT 10
does not and is quite fast. Somehow the optimizer is getting confused
by the "sid = 16". I was thinking that perhaps there is some magic
way of rephrasing the problem that MySQL would understand better.
Failing that, what is the recommended way of making this query
quicker? Should I insert another index, for instance called
negative_id that is always (4294967296 - id) or something like that,
so I can ORDER BY negative_id instead of ORDER BY id DESC?
Thank you for any advice or suggestions you might have.
--
Ben Escoto
---------------------------------------------------------------------
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
