MySQL won't use index for query?
- by Jack Sleight
I have this table:
CREATE TABLE `point` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`siteid` INT(11) NOT NULL,
`lft` INT(11) DEFAULT NULL,
`rgt` INT(11) DEFAULT NULL,
`level` SMALLINT(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `point_siteid_site_id` (`siteid`),
CONSTRAINT `point_siteid_site_id` FOREIGN KEY (`siteid`) REFERENCES `site` (`id`) ON DELETE CASCADE
) ENGINE=INNODB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
And this query:
SELECT * FROM `point` WHERE siteid = 1;
Which results in this EXPLAIN information:
+----+-------------+-------+------+----------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | point | ALL | point_siteid_site_id | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+----------------------+------+---------+------+------+-------------+
Question is, why isn't the query using the point_siteid_site_id index?
