Hi,
this bug was reported to fixed in 3.23.38. But the mysql vesions 3.23.49 and in 4.0.2 both look like the bug is in them again. Example: The following query should use the index on dog.dog_timestamp_cr but uses filesort instead. mysql> EXPLAIN SELECT dog.dog_id, dog.dog_name, sire.dog_name as dog_sirename FROM dogs dog LEFT JOIN dogs as sire ON (dog.dog_sireid = sire.dog_id) ORDER BY dog.dog_timestamp_cr LIMIT 100; +-------+--------+---------------+---------+---------+----------------+----- ---+----------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+----------------+----- ---+----------------+ | dog | ALL | NULL | NULL | NULL | NULL | 416827 | Using filesort | | sire | eq_ref | PRIMARY | PRIMARY | 3 | dog.dog_sireid | 1 | | +-------+--------+---------------+---------+---------+----------------+----- ---+----------------+ 2 rows in set (0.00 sec) Explain shows the column dog_timestamp_cr has an index: mysql> explain dogs; +-------------------+-----------------------+------+-----+---------+-------- --------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-----------------------+------+-----+---------+-------- --------+ | dog_timestamp_cr | timestamp(14) | YES | MUL | NULL ... best regards Gunnar --------------------------------------------------------------------- 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
