# Foreign Key (Mysql/Rails)

**URL:** <https://www.ruby-forum.com/t/foreign-key-mysql-rails/169868>\
**Category:** Rails\
**Created:** [June 9, 2009, 7:56pm UTC](https://www.ruby-forum.com/t/foreign-key-mysql-rails/169868 "2009-06-09T19:56:39Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![semyou](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ce7236/32.png) [@semyou](https://www.ruby-forum.com/u/semyou)\
**Post date:** [June 9, 2009, 7:56pm UTC](https://www.ruby-forum.com/t/foreign-key-mysql-rails/169868/1 "2009-06-09T19:56:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![semyou](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ce7236/32.png) [@semyou](https://www.ruby-forum.com/u/semyou)\
**Post date:** [June 9, 2009, 11:12pm UTC](https://www.ruby-forum.com/t/foreign-key-mysql-rails/169868/2 "2009-06-09T23:12:09Z")

</div>

I happen to be reading about this last night except I was reading about  
it in the PostgreSQL manual.

In the case of PostgreSQL, the planner searches for the least expensive  
plan and uses different plans based upon the “selectivity” of the  
constraint.

The PostgreSQL doc may give you a clue what MySQL is doing… maybe not.

> **[14.1. Using EXPLAIN](https://www.postgresql.org/docs/8.3/using-explain.html)**
>
> 14.1. Using EXPLAIN 14.1.1. EXPLAIN Basics 14.1.2. EXPLAIN ANALYZE 14.1.3. Caveats PostgreSQL devises a query plan for each query it receives. …

HTH,  
Perry

---

<div class="post-metadata">

**Author:** ![semyou](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ce7236/32.png) [@semyou](https://www.ruby-forum.com/u/semyou)\
**Post date:** [June 10, 2009, 3:52am UTC](https://www.ruby-forum.com/t/foreign-key-mysql-rails/169868/3 "2009-06-10T03:52:25Z")

</div>

Youyou S. wrote:

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

The constraint matters not, for performance. What is important is that  
the foreign key is indexed.

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

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

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

---

<div class="post-metadata">

**Author:** ![semyou](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ce7236/32.png) [@semyou](https://www.ruby-forum.com/u/semyou)\
**Post date:** [June 10, 2009, 9:25am UTC](https://www.ruby-forum.com/t/foreign-key-mysql-rails/169868/4 "2009-06-10T09:25:27Z")

</div>

On Jun 10, 2:52 am, Robert W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> 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