CODEWITHSUNDEEP
mysqldatabaseindexingforeign-keysinnodb
youyou
youyou

Reputation: 21

MySql Foreign Key index

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

Upvotes: 1

Views: 1028

Answers (1)

chaos
chaos

Reputation: 124267

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.

Upvotes: 4

Related Questions