Hi, I was assuming USE INDEX was only telling MySQL which INDEX it needed to use, but it seems to be not always the case.
With MySQL 4.1.10, USE INDEX(topic) for the following query seems to change the way the index is used. mysql> EXPLAIN SELECT numreponse FROM searchjoinhardwarefr13 WHERE id='24399' AND numreponse>='2307728' AND topic='26369'; +----+-------------+------------------------+------+------------------------ --+-------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+------+------------------------ --+-------+---------+-------+------+--------------------------+ | 1 | SIMPLE | searchjoinhardwarefr13 | ref | PRIMARY,numreponse,topic | topic | 3 | const | 117 | Using where; Using index | +----+-------------+------------------------+------+------------------------ --+-------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT numreponse FROM searchjoinhardwarefr13 USE INDEX(topic) WHERE id='24399' AND numreponse>='2307728' AND topic='26369'; +----+-------------+------------------------+-------+---------------+------- +---------+------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------------+-------+---------------+------- +---------+------+-------+--------------------------+ | 1 | SIMPLE | searchjoinhardwarefr13 | range | topic | topic | 10 | NULL | 17761 | Using where; Using index | +----+-------------+------------------------+-------+---------------+------- +---------+------+-------+--------------------------+ 1 row in set (0.00 sec) 'topic' is a compound index on (`topic`,`numreponse`,`id`) fields. Did I miss something about USE INDEX ? Is this the expected behaviour ? Thanks ! -- Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
