MySql Foreign Key index

Question

Hi,

I am using a Mysql table products which has a foreign key category_id on the categories table

the foreign key constraint is defined (Innodb engine).

I noticed that when I run EXPLAIN SELECT * from products where category_id=1;

it uses the foreign key, so I see type=Range and Key: my_foreign_key

But when I run EXPLAIN SELECT * from products where category_id IN (1,10);

it uses a full table scan: type=ALL, Key:NULL!!!

Ironically, when I do EXPLAIN SELECT * from products where category_id IN (1,2); It uses type-range and Key: My_foreign_key!

So I guess there is a problem when the category_id uses values that are not contiguous.

Any ideas why?

Thanks

Answer 1

This is one form of the perennial question "why does MySQL stop using indexes when my table gets big?" Basically, there's a point past which using indexes stops being helpful and starts hurting your query, and MySQL's query optimizer is designed to take that into account. If you don't believe it, you can use FORCE INDEX to make it use a particular index, run your query with and without the index and see what results you get.

The different in behavior with (1,2) and (1,10) is probably because category 2 is much more heavily used than category 10.

Answer 2

Thanks, but the bad news is that my category_id table isn't big! Does this change anything?