Foreign Key (Mysql/Rails)

Of course, there is no index in the foreign key so MySQL has no way to optimize.

When you add a foreign key mysql implicitly adds an index.

> 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.

Not ironic to me. I assume MySQL optimization recognizes the contiguous nature of the values and applies the appropriate optimization.

It can be more complicated than that. Assume for example that categories is a small table. Mysql might decide that the cost of the random seeks to get the index for id=1 and then the corresponding row from the categories table, then going back to the index and seeking for id=2 and then going back to the categories table is actually smaller than just reading the whole of the categories table in one go. There are a lot of factors in this sort of thing although mysql can get it wrong. If you thing you know better, there's always USE INDEX (...) or FORCE INDEX(...) to tell mysql that it really should try and use the index you are telling it to.

Fred