>>>>> "SM" == Sinisa Milivojevic <[EMAIL PROTECTED]>
>>>>> wrote the following on Sun, 3 Jun 2001 21:31:48 +0300 (EEST)
>> 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
SM> Can you send me output of EXPLAIN for both queries.
For the first:
+---------------+------+---------------+-------+---------+-------+--------+-----------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+---------------+------+---------------+-------+---------+-------+--------+-----------------------------------------+
| sequence_data | ref | sidid | sidid | 4 | const | 124561 | where
|used; Using index; Using filesort |
+---------------+------+---------------+-------+---------+-------+--------+-----------------------------------------+
And the second:
+---------------+-------+---------------+-------+---------+------+--------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+---------------+-------+---------------+-------+---------+------+--------+-------------------------+
| sequence_data | index | NULL | sidid | 8 | NULL | 155292 | where
|used; Using index |
+---------------+-------+---------------+-------+---------+------+--------+-------------------------+
And a "control" case:
EXPLAIN SELECT * FROM sqmail.sequence_data ORDER BY sid DESC,id DESC LIMIT 10;
+---------------+-------+---------------+-------+---------+------+--------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+---------------+-------+---------------+-------+---------+------+--------+-------------+
| sequence_data | index | NULL | sidid | 8 | NULL | 155292 | Using
|index |
+---------------+-------+---------------+-------+---------+------+--------+-------------+
ryc> Perhaps ORDER BY (column * -1), if the column is numeric would
ryc> be sufficient. But that might also make the optimizer less
ryc> willing to use the index for sorting. Also I think MySQL will
ryc> use filesort when the returned result is large, but I could be
ryc> wrong.
Yep, thats a good idea, but even for simple queries it seems to turn
indexing off. For instance, on a table with 120000 rows indexed by
id, this:
SELECT id FROM headers ORDER BY (id*-1) LIMIT 10;
took 100+ times as long as the equivalent:
SELECT id FROM headers ORDER BY id DESC LIMIT 10;
JDZ> Did you try (sid > 15 and sid < 17), or something that means
JDZ> the same thing as (sid = 16) if sid is an int?
JDZ> Don't know if it'll help, but you never know...
Yep, I thought that was worth a try and attempted various
combinations. It seemed that any comparison on the sid column caused
the use of filesort.
--
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
