CODEWITHSUNDEEP
mysqlquery-optimization
Silver Light
Silver Light

Reputation: 45902

MySQL query optimization: IN() vs OR

I've been reading, that MySQL has a problem with queries that use IN() statement - sometimes indexes can't be used. Is that really so, if I don't use a subquery?

Which approach is better? Is there a performance difference?

1

SELECT *
FORM `somewhere`
WHERE 
  `id` = 3
   OR `id` = 5
   OR `id` = 15
   OR `id` = 56
   OR `id` = 34
   OR `id` = 47

2

SELECT *
FORM `somewhere`
WHERE 
  `id` IN (3,5,15,56,34,47)

Upvotes: 7

Views: 2979

Answers (2)

cristian
cristian

Reputation: 8744

If you really want to use indexes you can use UNION read more here mysql-followup-on-union-for-query-optimization-query-profiling

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 837926

The second approach is better. MySQL can optimize this.

MySQL has a problem with queries that use IN() statement - sometimes indexes can't be used. Is that really so, if I don't use a subquery?

There can be a problem with IN when you write IN(SELECT ...), but I don't think there is a problem with a simple list of values.

Upvotes: 9

Related Questions