this sucks. i just realized that MySQL isn't using the index for the ORDER BY when i really wish it would.

examples: these use the index:

Code:
mysql> EXPLAIN SELECT * FROM users ORDER BY userid LIMIT 10;
+---------+-------+---------------+---------+---------+------+------+-------+
| table   | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+---------+-------+---------------+---------+---------+------+------+-------+
| users   | index | NULL          | PRIMARY |       2 | NULL |  527 |       |
+---------+-------+---------------+---------+---------+------+------+-------+

mysql> EXPLAIN SELECT * FROM users ORDER BY userid DESC LIMIT 10;
+---------+-------+---------------+---------+---------+------+------+-------+
| table   | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+---------+-------+---------------+---------+---------+------+------+-------+
| users   | index | NULL          | PRIMARY |       2 | NULL |  527 |       |
+---------+-------+---------------+---------+---------+------+------+-------+

mysql> EXPLAIN SELECT * FROM users WHERE userid > 100 ORDER BY userid LIMIT 10;
+---------+-------+---------------+---------+---------+------+------+------------+
| table   | type  | possible_keys | key     | key_len | ref  | rows | Extra      |
+---------+-------+---------------+---------+---------+------+------+------------+
| users   | range | PRIMARY       | PRIMARY |       2 | NULL |  527 | where used |
+---------+-------+---------------+---------+---------+------+------+------------+

mysql> EXPLAIN SELECT * FROM users WHERE userid < 100 ORDER BY userid LIMIT 10;
+---------+-------+---------------+---------+---------+------+------+------------+
| table   | type  | possible_keys | key     | key_len | ref  | rows | Extra      |
+---------+-------+---------------+---------+---------+------+------+------------+
| users   | range | PRIMARY       | PRIMARY |       2 | NULL |   78 | where used |
+---------+-------+---------------+---------+---------+------+------+------------+
great. these don't however:

Code:
mysql> EXPLAIN SELECT * FROM users WHERE userid < 100 ORDER BY userid DESC LIMIT 10;
+---------+-------+---------------+---------+---------+------+------+----------------------------+
| table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                      |
+---------+-------+---------------+---------+---------+------+------+----------------------------+
| users   | range | PRIMARY       | PRIMARY |       2 | NULL |   78 | where used; Using filesort |
+---------+-------+---------------+---------+---------+------+------+----------------------------+

mysql> EXPLAIN SELECT * FROM users WHERE userid > 100 ORDER BY userid DESC LIMIT 10;
+---------+-------+---------------+---------+---------+------+------+----------------------------+
| table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                      |
+---------+-------+---------------+---------+---------+------+------+----------------------------+
| users   | range | PRIMARY       | PRIMARY |       2 | NULL |  527 | where used; Using filesort |
+---------+-------+---------------+---------+---------+------+------+----------------------------+
Using filesort. i could almost understand the last one (which is the kind i want to use the index), but i still don't get why it won't use the index for `ORDER BY key DESC' when you have a range and it does for just `ORDER BY key'.

i guess there's not really an answer, but i wanted to see if anybody had anything to say. would it work in other databases?


oh BTW, kind of a related question. when you want to sort in the order rows were INSERTed, is it OK to use the AUTO_INCREMENT PRIMARY KEY since it should always be "in order?" or is that bad and you should ORDER BY a timestamp, etc.? on a message board, for example, the latest posts should always have the highest ID, so is it OK to ORDER BY that? seems all right to me, since "older" deleted IDs shouldn't be reused. let me know.