CODEWITHSUNDEEP
mysql
Satya Kalluri
Satya Kalluri

Reputation: 5214

Is there any performance difference between IN and = operators in MySQL

I have a MySQL query where in the age is to checked against an array of possible-possible-values.

The query looks like :

select * from users where age in (15,18,20,22);

Some times, the ages-array might only have a single value, say [22].

In such circumstances, am wondering if the following query (A):

select * from users where age in (22);

will be optimum when compared to (B):

select * from users where age = 22;

Please, let me know your thoughts.

Upvotes: 2

Views: 1805

Answers (4)

Icarus
Icarus

Reputation: 63956

There shouldn't be a performance impact on using 

... Id in (22)

Vs

... Id=22

The execution plan for both statements will be identical, the IN statement will be transformed to '=22' when the list in the IN only has one element.

Upvotes: 1

Rahul
Rahul

Reputation: 77876

I don't believe that there is any performance difference between IN and = operators in MySQL.

We use = operator when trying to compare a single element and when we have to compare list of elements we use IN clause.

Anyways, IN clause internally gets flatten as series of OR condition/OR'ed stack ... like

col1 IN('a','b','c')

Will become

col = 'a' OR col1 = 'b' OR col = 'c'

Upvotes: 1

John Woo
John Woo

Reputation: 263723

You can use IN clause to replace many OR conditions. So

SELECT * FROM colA = 'Value1' or colA = 'Value2'

is the same as

SELECT * FROM colA IN ('Value1', 'Value2')

Upvotes: 0

Uday Sawant
Uday Sawant

Reputation: 5798

As much i know IN clause is internally implemented as a series of where clauses like 

WHERE id = 1 OR id = 2 OR id = 3

Please correct me if I am wrong...

Upvotes: 6

Related Questions