On Sun, Jun 03, 2001 at 11:01:50AM -0700, Ben Escoto wrote:
> >>>>> "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.
Did you try (sid > 15 and sid < 17), or something that means the same
thing as (sid = 16) if sid is an int?
Don't know if it'll help, but you never know...
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 439-9951
MySQL 3.23.29: up 9 days, processed 53,444,771 queries (68/sec. avg)
---------------------------------------------------------------------
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
