von,
Wednesday, July 31, 2002, 3:06:12 PM, you wrote:
vB> this bug was reported to fixed in 3.23.38.
vB> But the mysql vesions 3.23.49 and in 4.0.2
vB> both look like the bug is in them again.
vB> Example:
vB> The following query should use the index on
vB> dog.dog_timestamp_cr but uses filesort instead.
VB> mysql> EXPLAIN SELECT dog.dog_id, dog.dog_name, sire.dog_name as
vB> dog_sirename FROM dogs dog LEFT JOIN dogs as sire ON (dog.dog_sireid =
vB> sire.dog_id) ORDER BY dog.dog_timestamp_cr LIMIT 100;
vB> +-------+--------+---------------+---------+---------+----------------+-----
vB> ---+----------------+
vB> | table | type | possible_keys | key | key_len | ref | rows
vB> | Extra |
vB> +-------+--------+---------------+---------+---------+----------------+-----
vB> ---+----------------+
vB> | dog | ALL | NULL | NULL | NULL | NULL |
vB> 416827 | Using filesort |
vB> | sire | eq_ref | PRIMARY | PRIMARY | 3 | dog.dog_sireid |
vB> 1 | |
vB> +-------+--------+---------------+---------+---------+----------------+-----
vB> ---+----------------+
vB> 2 rows in set (0.00 sec)
VB> Explain shows the column dog_timestamp_cr has an index:
VB> mysql> explain dogs;
vB> +-------------------+-----------------------+------+-----+---------+--------
vB> --------+
vB> | Field | Type | Null | Key | Default | Extra
vB> |
vB> +-------------------+-----------------------+------+-----+---------+--------
vB> --------+
vB> | dog_timestamp_cr | timestamp(14) | YES | MUL | NULL
vB> ...
I gave not enough info about indexes. Use
SHOW INDEX FROM table_name
intead of EXPLAIN table_name.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
---------------------------------------------------------------------
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
