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

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)?

Answer 1

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. :-)

Answer 2

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.