Ben Escoto writes:
>
> 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
>
One more thing.
Can you try to run ANALYZE TABLE on sequence_data and see if that
changes anything ...
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
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
