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]
