Ken, The problem is that you've got a compound index on files which type_id isn't the first item. If you create a new index on files, just on type_id, all will be fine.
Mysql would only be able to use a compound index for this query if type_id was the first column in it. Andy > -----Original Message----- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: 09 September 2003 18:19 > To: [EMAIL PROTECTED] > Subject: Query won't use index > > > I have a query that won't seem to use an index. See below for > the EXPLAIN, the tables and the indexes (relevant fields only, so > no need to ask me why I'm bothering to do a query with nothing > else in it). > > Note that if I change "select t.Desc" to "select t.type_id", then > MySQL correctly uses the index. > > What am I missing? > > ------------------------- > > explain > select t.Desc > from files f, types t > where t.type_id = f.type_id > > | t | ALL | PRIMARY,type_id | NULL | NULL | > NULL | 3 | | > | f | ref | type_id | type_id | 4 | t.type_id | > 2322 | Using index | > > mysql> describe types; > | Field | Type | Null | Key | Default | Extra | > +--------------------+----------+------+-----+---------+----------------+ > | type_id | int(11) | | PRI | NULL | auto_increment | > | Desc | char(6) | YES | | NULL | | > > mysql> describe files; > --+----------------+ > | Field | Type | Null | Key | > Default | Extra | > +------------------------+---------------------+------+-----+-------- > | id | int(11) | | PRI | NULL | > auto_increment | > | type_id | int(11) | | MUL | 1 > > mysql> show index from files; > | Table | Non_unique | Key_name | > Seq_in_index | Column_name | Collation | Cardinality | > Sub_part | Packed | Comment | > --------------+-----------+-------------+----------+--------+---------+ > | files | 0 | PRIMARY | 1 | id > | A | 6965 | NULL | NULL | | > | files | 1 | id | 1 | id > | A | 6965 | NULL | NULL | | > | files | 1 | type_id | 1 | type_id > | A | 2 | NULL | NULL | | > > mysql> show index from types; > | Table | Non_unique | Key_name | Seq_in_index | > Column_name| Collation | Cardinality | Sub_part | Packed | Comment | > +-----------+-------------+----------+--------+---------+ > | types | 0 | PRIMARY | 1 | type_id | > A | 3 | NULL | NULL | | > | types | 1 | type_id | 1 | type_id | A > | NULL | NULL | NULL | | > > ---------------- > > Thanks in advance! > > - Ken > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
