MySQL Performance – “IN” Clause vs. Equals (=) for a Single Value [duplicate]

by

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 <code>= 1</code>

EXPLAIN for a query with IN(1):

Explain for a query with <code>IN(1)</code>

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

Explain for a query with <code>IN(1,2,3)</code>

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. 🙂

More Related Contents:

  1. Doing calculations in MySQL vs PHP
  2. Geo-Search (Distance) in PHP/MySQL (Performance)
  3. MySQL Stored Procedure vs. complex query
  4. MySQL Query IN() Clause Slow on Indexed Column
  5. How to get the value which user has selected in php?
  6. for loop is not executed /getting error like loop is not working
  7. Organization db for blog posts with Mysql and Php [closed]
  8. PHP – Change value y,n in database [closed]
  9. I can’t view the database content on a php page [closed]
  10. mysqli or PDO – what are the pros and cons? [closed]
  11. mysqli_stmt::bind_result(): Number of bind variables doesn’t match number of fields in prepared statement
  12. Undefined function mysql_connect()
  13. “Connect failed: Access denied for user ‘root’@’localhost’ (using password: YES)” from php function [duplicate]
  14. How to resolve ambiguous column names when retrieving results?
  15. mysqli::mysqli(): (HY000/2002): Can’t connect to local MySQL server through socket ‘MySQL’ (2)
  16. What is the difference between MySQL, MySQLi and PDO? [closed]
  17. Fatal error: [] operator not supported for strings
  18. Maximum execution time in phpMyadmin
  19. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  20. PHP PDO – Num Rows
  21. Post and get at the same time in php
  22. Setting up Laravel on a Mac php artisan migrate error: No such file or directory [duplicate]
  23. Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted
  24. Codeigniter Transactions
  25. PDOException “could not find driver” in php
  26. Error while using PDO prepared statements and LIMIT in query [duplicate]
  27. SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
  28. Coverting Hex to Image in PHP?
  29. How to check if a value already exists to avoid duplicates?
  30. How to use MySQL Found_Rows() in PHP?

Leave a Comment