MySQL Performance - "IN" Clause vs. Equals (=) for a Single Value

Question

This is a pretty simple question and I'm assuming the answer is "It doesn't matter" but I have to ask anyway...

I have a generic sql statement built in PHP:

$sql = 'SELECT * FROM `users` WHERE `id` IN(' . implode(', ', $object_ids) . ')';

Assuming prior validity checks ($object_ids is an array with at least 1 item and all numeric values), should I do the following instead?

if(count($object_ids) == 1) {
    $sql = 'SELECT * FROM `users` WHERE `id` = ' . array_shift($object_ids);
} else {
    $sql = 'SELECT * FROM `users` WHERE `id` IN(' . implode(', ', $object_ids) . ')';
}

Or is the overhead of checking count($object_ids) not worth what would be saved in the actual sql statement (if any at all)?

You answered your question :) it doesn't matter. It's also easier to use `implode` without checking the size of the array. — N.B., Mar 29 '12 at 13:36
@N.B. Did anyone ever tell you your name is perfect for comments :P — mellamokb, Mar 29 '12 at 13:38
SELECT * FROM users u INNER JOIN (SELECT 1 as id UNION SELECT 11 as id UNION SELECT 21 as id ) t2 ON u.id = t2.id — Николай Лубышев, Apr 16 '21 at 09:00

J.D. · Answer 1 · 2017-09-22T21:32:55.743

Most of the other answers don't provide anything conclusive, just speculation. So, based on the good advice from @Namphibian's answer, I ran an EXPLAIN on some queries similar to the ones in the OP.

The results are below:

EXPLAIN for a query with = 1:

EXPLAIN for a query with IN(1):

EXPLAIN for a query with IN(1,2,3):

As you can see, MySQL does optimize IN(1) to be the same as = 1 in this sort of query. @mes's answer seems to indicate that this might not always be the case with more complex queries, however.

So, for those who were too lazy to run the EXPLAIN themselves, now you know. And yes, you may want to run the EXPLAIN on your own query to be sure that it is handled this way. :-)

score 29 · Answer 2 · answered Mar 29 '12 at 13:37

29

There is no difference between the MySQL statements, and the MySQL optimiser will transform the IN to the = when IN is just one element. Don't bother.

answered Mar 29 '12 at 13:37

Konerak

39,272
12
98
118

7

Could you bring up some MySQL official information to confirm your assertion? Are you completely sure that MySQL optimizer transform it as you explained? – Delmo Mar 09 '14 at 13:28
3

It's not true, I run query with explain statement and posted screenshots as an answer – mes Oct 29 '14 at 08:23

score 18 · Accepted Answer · answered Mar 29 '12 at 13:36

18

Neither of them really matter in the big scope of things. The network latency in communicating with the database will far outweigh either the count($object_ids) overhead or the = vs IN overhead. I would call this a case of premature optimization.

You should profile and load-test your application to learn where the real bottlenecks are.

answered Mar 29 '12 at 13:36

mellamokb

56,094
12
110
136

3

This did not really answer the question!! – Phung D. An Sep 27 '17 at 18:34

score 10 · Answer 4 · answered Mar 29 '12 at 13:44

Run the two queries with a explain statement. This will show you what MySQL is doing. You focus on MySQL optimisation should be on what is MySQL doing with the query internally. Trying to optimise which query gets executed is a bit premature.

Both these queries could be terrible in performance if there is no index for example. MySQL's EXPLAIN statement is gold here. So when you get to a query that is running slow the EXPLAIN statement will show you why.

score 2 · Answer 5 · answered Mar 29 '12 at 13:38

2

I imagine that internally mysql will treat the IN (6) query exactly as a = 6 query so there is no need to bother (this is called premature optimization by the way)

answered Mar 29 '12 at 13:38

Nicola Peluchetti

76,206
31
145
192

score 1 · Answer 6 · answered Oct 09 '14 at 09:43

1

I run query with explain statement and here are the results enter image description here

It's obvious that "Equals" operator is better, it scans 13 row, and "IN" scans all rows

answered Oct 09 '14 at 09:43

mes

3,581
29
28

5

Your query has a subquery so it's not necessarily an indication of the optimization of `IN` with one item – Vadim Jun 23 '15 at 03:46

MySQL Performance - "IN" Clause vs. Equals (=) for a Single Value

6 Answers6

Linked

Related