MySQL won't use index for query?

Question

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?

I recreated your table and it clearly uses the intended index. — Nirmal
– Nirmal, Commented Apr 3, 2010 at 15:29
Certainly didn't for me, that is the exact EXPLIAN output I received for that query. — Jack Sleight
– Jack Sleight, Commented Apr 6, 2010 at 21:12

Petar Minchev · Accepted Answer · 2010-04-03 15:17:24Z

2

I haven't used MySQL much, but in PostgreSQL if the number of records is small in the table, it can decide not to use the index. This is not a problem, because it chooses the best query plan for the situation. When the number of records is bigger, it will use the index. Maybe this is the same case here with MySQL.

Edit: Are you sure the foreign key is indexed?

answered Apr 3, 2010 at 15:17

Petar Minchev

47.5k12 gold badges107 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marc B Over a year ago

Recent Mysql versions will auto-create keys on any field defined as a foreign key, instead of making you create it seperately.

dkretz · Accepted Answer · 2010-04-03 15:21:10Z

0

It's probably because you don't have enough sample data, or the cardinality (diversity) of the siteid values isn't very large. These are the most common reasons the optimizer will find it quicker just to read all the values.

answered Apr 3, 2010 at 15:21

dkretz

37.6k13 gold badges84 silver badges140 bronze badges

Collectives™ on Stack Overflow

MySQL won't use index for query?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related