Performance differences between equal (=) and IN for mysql

Published on Author Code Father
Performance differences between equal (=) and IN for mysql

There is no difference between those two statements, and the optimiser will transform the IN to the = when IN have just one element in it.

Though when you have a question like this, just run both statements, run their execution plan and see the differences. Here – you won’t find any.

After a big search online, I found a document on SQL to support this(I assume it applies to all DBMS):

If there is only one value inside the parenthesis, this commend is equivalent to

WHERE “column_name” = ‘value1

Here is the link to the document.

Here is the execution plan of both queries in Oracle (Most DBMS will process this the same) :

EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number = '123456789'

Plan hash value: 2312174735
-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
|   2 |   INDEX UNIQUE SCAN         | SYS_C0029838  |-----------------------------------------------------

And for IN() :

EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number in('123456789');

Plan hash value: 2312174735
-----------------------------------------------------
| Id  | Operation                   | Name          |
-----------------------------------------------------
|   0 | SELECT STATEMENT            |               |
|   1 |  TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
|   2 |   INDEX UNIQUE SCAN         | SYS_C0029838  |
-----------------------------------------------------

As you can see, both are identical. This is on an indexed column. Same goes for an unindexed column (just full table scan) .

Comments

comments