I have two tables with exactly the same structure : CREATE TABLE threadhardwarefr9 (numeropost bigint(20) unsigned DEFAULT '0' NOT NULL, icone tinyint(2) unsigned DEFAULT '0' NOT NULL, numreponse bigint(20) unsigned NOT NULL auto_increment, contenu text NOT NULL,pseudo varchar(35) NOT NULL, date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, signature tinyint(1) unsigned DEFAULT '0' NOT NULL, ip varchar(15) NOT NULL, PRIMARY KEY (numreponse), KEY numeropost (numeropost), KEY pseudo (pseudo), KEY ip (ip)); Here is what I get with a EXPLAIN on one of my query : On table 7 : mysql> EXPLAIN SELECT pseudo FROM threadhardwarefr7 WHERE numeropost='6027' ORDER BY numreponse ASC LIMIT 0,30; +-------------------+------+---------------+------------+---------+-------+--- ----+----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------------+------+---------------+------------+---------+-------+--- ----+----------------------------+ | threadhardwarefr7 | ref | numeropost | numeropost | 8 | const | 10358 | where used; Using filesort | +-------------------+------+---------------+------------+---------+-------+--- ----+----------------------------+ 1 row in set (0.00 sec) On table 12 : mysql> EXPLAIN SELECT pseudo FROM threadhardwarefr12 WHERE numeropost='1' ORDER BY numreponse ASC LIMIT 0,30; +--------------------+-------+---------------+------------+---------+------+-- -----+----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------------+-------+---------------+------------+---------+------+-- -----+----------------------------+ | threadhardwarefr12 | range | numeropost | numeropost | 8 | NULL | 10181 | where used; Using filesort | +--------------------+-------+---------------+------------+---------+------+-- -----+----------------------------+ 1 row in set (0.00 sec) The query are exactly the same, so why mysql optimizer choses the ref type for the first query, and the range type for the second ?? (I run OPTIMIZE TABLE and ANALYZE TABLE on both TABLE before the query) Thanks ! Jocelyn Fournier Presence-PC www.presence-pc.com
