0

I have a Rails 3.2 app where I am trying to grab a group of users. My Rails code for the query looks like:

@users = User.where("user_id IN (?)", foo)

which generates the following SQL:

SELECT 'users'.* FROM 'users' WHERE id IN (1, 2, 3, 4)

When I run an explain on this query, it is searching all rows in my users table. It lists PRIMARY under the possible_keys field, but the key is showing as null.

If I remove the "IN (?)" part and pull just one record:

@user = User.where("user_id = ?", bar)

generating

SELECT 'users'.* FROM 'users' WHERE id = 1

then the query is executed using the primary key as the index and searching only a single row.

Any idea why this isn't working in my first example? Am I structuring this query incorrectly in Rails?

Jim
  • 2,300
  • 1
  • 19
  • 43

1 Answers1

0

The problem not is in Rails, this is the "how" write your sql.

When you use the clause "in" the query don't use the index for it.

You can check the follow links:

MySQL not using indexes with WHERE IN clause?

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

Community
  • 1
  • 1
Francisco Jacob
  • 128
  • 2
  • 11