Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
What happened when you tried? –  a_horse_with_no_name Feb 14 at 13:28
    
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 at 15:28
    
This is not a bad question in itself if you answer it from an internals perspective, which I just did. –  RolandoMySQLDBA Feb 14 at 20:52
    
@a_horse_with_no_name - I agree the OP should attempt to answer the question, and not just post a question without any attempt to find out. Having said that, it might be quite difficult to find a performance difference between these two statements without having access to a large amount of data. –  Max Vernon Feb 14 at 23:36
    
@MaxVernon: that special case of IN vs. = for a single value should be absolutely identical. If it isn't then the MySQL query optimizer is even worse than I thought. –  a_horse_with_no_name Feb 15 at 8:39

2 Answers 2

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  = ...;
share|improve this answer
1  
How do you know? –  Max Vernon Feb 14 at 15:14
    
"=" and "IN" returns boolean types, and the only difference is that "IN" operator allows you to specify multiple values in a WHERE clause –  dimitar Feb 14 at 15:55
    
IMHO your answer is oversimplified. Yet, for all intents and purposes, your answer is essentially true. I added a post explaining why. +1 for you. –  RolandoMySQLDBA Feb 14 at 19:52
    
@dimitar - I wasn't asking for the difference in semantics or usage of the two clauses. I was asking how do you know the performance is the same; clearly the code path followed by the server is not precisely the same, therefore I doubt the performance is identical. Hence my question still stands, "how do you know?" –  Max Vernon Feb 14 at 23:32
    
@RolandoMySQLDBA - Although I agree that in practice the performance may be similar, I don't agree with promoting non-answers, or answers that don't provide some kind of evidence or proof, be it a code sample, or whatever. –  Max Vernon Feb 14 at 23:34

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.

share|improve this answer
    
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 at 8:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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