I'm cleaning up someone else's code. In going through the very complex system which generates very complex SQL queries, I see a lot of usage of the IN() WHERE clause:
SELECT * FROM table
WHERE field1 IN( 1, 2, 3, 4, 5 )
AND type IN( 1, 2, 3 )
... and so on. Is it better for performance to assemble a series of field = 1 OR field = 2 OR...
like this:
SELECT * FROM table
WHERE ( field1 = 1 OR field1 = 2 OR field1 = 3 OR field1 = 4 OR field1 = 5 )
AND ( type = 1 OR type = 2 OR type = 3 )
or is it better to use IN()? Even for a single value like type IN(1)
?
The answer varies by version of MySQL. The developers have worked on improving the optimizer with each major release, so the efficiency of OR versus IN could be quite different in MySQL 5.6 or later. It also depends on how many terms are we talking about, and index selectivity and so on.
The best advice is to benchmark and profile your real queries, on your installed MySQL version, using your real data.