Hi, I wonder how the optimizer works. Here is what I obtain with two of my queries : mysql> EXPLAIN SELECT titre,forumconthardwarefr7.numeropost,auteur,forumconthardwarefr7.icone,nbrep,maxnumrep,forumconthardwarefr7.date,vue,ouvert,lastauteur FROM forumconthardwarefr7,threadhardwarefr7 WHERE forumconthardwarefr7.numeropost=threadhardwarefr7.numeropost AND threadhardwarefr7.contenu LIKE '%test%' AND threadhardwarefr7.date>='2001-04-25' ORDER BY forumconthardwarefr7.date DESC LIMIT 0,30; +----------------------+--------+-----------------+---------+---------+------------------------------+-------+---------------------------------------------+ | table | type | possible_keys | key | key_len | ref | | rows | Extra | +----------------------+--------+-----------------+---------+---------+------------------------------+-------+---------------------------------------------+ | threadhardwarefr7 | range | numeropost,date | date | 8 | NULL | | 51022 | where used; Using temporary; Using filesort | | forumconthardwarefr7 | eq_ref | PRIMARY | PRIMARY | 8 | |threadhardwarefr7.numeropost | 1 | | +----------------------+--------+-----------------+---------+---------+------------------------------+-------+---------------------------------------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT titre,forumconthardwarefr7.numeropost,auteur,forumconthardwarefr7.icone,nbrep,maxnumrep,forumconthardwarefr7.date,vue,ouvert,lastauteur FROM forumconthardwarefr7,threadhardwarefr7 WHERE forumconthardwarefr7.numeropost=threadhardwarefr7.numeropost AND threadhardwarefr7.contenu LIKE '%test%' AND threadhardwarefr7.date>='2001-04-20' ORDER BY forumconthardwarefr7.date DESC LIMIT 0,30; +----------------------+------+-----------------+------------+---------+---------------------------------+-------+----------------+ | table | type | possible_keys | key | key_len | ref | | rows | Extra | +----------------------+------+-----------------+------------+---------+---------------------------------+-------+----------------+ | forumconthardwarefr7 | ALL | PRIMARY | NULL | NULL | NULL | | 12289 | Using filesort | | threadhardwarefr7 | ref | numeropost,date | numeropost | 8 | |forumconthardwarefr7.numeropost | 20 | where used | +----------------------+------+-----------------+------------+---------+---------------------------------+-------+----------------+ 2 rows in set (0.00 sec) Can anyone explain to me why the optimizer makes these choices which seem to me so different... Thanks, Jocelyn Fournier Presence-PC www.presence-pc.com
