Youyou Semsem 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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
