MySQL won't use index for query?

Posted by Jack Sleight on Stack Overflow See other posts from Stack Overflow or by Jack Sleight
Published on 2010-04-03T15:07:10Z Indexed on 2010/04/03 15:13 UTC
Read the original article Hit count: 266

Filed under:
|
|
|

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?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about index