4

Are there any performance differences between

SELECT * 
FROM table 
WHERE id IN (42);

and

SELECT * 
FROM table 
WHERE id = 42;

?

The question is about just supplying a single value. The natural assumption for me is that the optimizer will treat them as equal and perform the same optimization when fetching the data. But is that correct?

  • you should give more details. your question is very abstract and does not indicate your intention. Are you asking "under the hood" how MySql executes "=" Vs. "IN" operator. or is it just your specific example in your env. whta is the size of table? what are the other column on the table etc.... lot of things affect the databases optimization decision. if you are more interested in knowing answer for first option please update the question more appropriately. – Anup Shah Feb 14 '14 at 15:28
  • I'm sorry that it was to abstract. But as @a_horse_with_no_name pointed out the question is about a single value not IN vs = in general. – Jan Seevers Feb 15 '14 at 9:45
3

Whether the queries are equivalent or not is up to the MySQL Query Optimizer. Why ?

Back n Mar ,13 2013 I wrote an answer to this post: Is there an execution difference between a JOIN condition and a WHERE condition?

In that post I described exactly how JOINs are executed. The following is taken from my post which quotes from page 172 of Understanding MySQL Internals:

  • Determine which keys can be used to retrieve the records from tables, and choose the best one for each table.
  • For each table, decide whether a table scan is better that reading on a key. If there are a lot of records that match the key value, the advantages of the key are reduced and the table scan becomes faster.
  • Determine the order in which tables should be joined when more than one table is present in the query.
  • Rewrite the WHERE clauses to eliminate dead code, reducing the unnecessary computations and changing the constraints wherever possible to the open the way for using keys.
  • Eliminate unused tables from the join.
  • Determine whether keys can be used for ORDER BY and GROUP BY.
  • Attempt to simplify subqueries, as well as determine to what extent their results can be cached.
  • Merge views (expand the view reference as a macro)

On that same page, it says the following:

In MySQL optimizer terminology, every query is a set of joins. The term join is used here more broadly than in SQL commands. A query on only one table is a degenerate join. While we normally do not think of reading records from one table as a join, the same structures and algorithms used with conventional joins work perfectly to resolve the query with only one table.

From the aforementioned information, JOIN behavior will execute the same say regardless of a query having multiple tables or event just one table.

YOUR ORIGINAL QUESTION

Under the hood, MySQL will evaluate the two queries the same way. If you want better query performance, you have to take the bull by the horns. You should do all you can to the table so that MySQL join behavior goes as smooth as possible.

  • Add the needed indexes
  • Increase session level buffers (sort_buffer_size, join_buffer_size)
  • Take advantage of Storage Engine mechanisms for tuning data and indexes
  • Refactor the query

If you look at dimitar's answer, now it spells out a case where MySQL's join behavior is put to the test. Instead of betting on two horses you own (your queries) to see who runs better, invest time into getting a faster horse if such a horse exists.

From ditimar's post, you have these

  • SELECT * FROM table WHERE id IN (42,43,44,45);
  • SELECT * FROM table WHERE id = 42 or id = 43 or id = 44 or id = 45;

Here is yet another one I suggest for the sake of example

SELECT A.* FROM table A INNER JOIN
(SELECT 42 id UNION SELECT 43 UNION SELECT 44 UNION SELECT 45) B
USING (id);

and another

SELECT * FROM table WHERE id = 42
UNION
SELECT * FROM table WHERE id = 43
UNION
SELECT * FROM table WHERE id = 44
UNION
SELECT * FROM table WHERE id = 45;

I can make up other possibilities, but the main idea here is to try to write good queries the first time. When your amount of data grows, your best queries may suffer due to key distribution and stale index stats which may require optimizing tables or even rewriting queries to suit bigger data.

  • 1
    Interesting insight, but the question was about a single value, not multiple values. Is there still a difference then? (which I would find hard to believe) – a_horse_with_no_name Feb 15 '14 at 8:40
1
SELECT * FROM table WHERE id IN (42,43,44,45,...);

is equivalent to

SELECT * FROM table WHERE id = 42 or id = 43 or id = 44 or id  = ...;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.