MySQL Performance - IN Clause vs. Equals (=) for a Single Value
Neither of them really matter in the big scope of things. The network latency in communicating with the database will far outweigh either the count($object_ids)
overhead or the =
vs IN
overhead. I would call this a case of premature optimization.
You should profile and load-test your application to learn where the real bottlenecks are.
Performance differences between equal (=) and IN with one literal value
There is no difference between those two statements, and the optimiser will transform the IN
to the =
when IN
has just one element in it.
Though when you have a question like this, just run both statements, run their execution plan and see the differences. Here - you won't find any.
After a big search online, I found a document on SQL to support this (I assume it applies to all DBMS):
If there is only one value inside the parenthesis, this commend [sic] is equivalent to,
WHERE "column_name" = 'value1
Here is the execution plan of both queries in Oracle (most DBMS will process this the same):
EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number = '123456789'
Plan hash value: 2312174735
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
| 2 | INDEX UNIQUE SCAN | SYS_C0029838 |
-----------------------------------------------------
And for IN()
:
EXPLAIN PLAN FOR
select * from dim_employees t
where t.identity_number in('123456789');
Plan hash value: 2312174735
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| DIM_EMPLOYEES |
| 2 | INDEX UNIQUE SCAN | SYS_C0029838 |
-----------------------------------------------------
As you can see, both are identical. This is on an indexed column. Same goes for an unindexed column (just full table scan).
Potsgres Performance: WHERE = Versus WHERE IN (...) for single values
TLDR: It does not make a difference, the performance will always be the same, therefore the IN
is a clear winner for your use case.
A quick test to verify this could be done:
test=# CREATE table test_in (id serial primary key);
CREATE TABLE
test=# explain select * from test_in where id = '1';
QUERY PLAN
---------------------------------------------------------------------------------
Index Only Scan using test_in_pkey on test_in (cost=0.15..2.17 rows=1 width=4)
Index Cond: (id = 1)
(2 rows)
test=# explain select * from test_in where id in ('1');
QUERY PLAN
---------------------------------------------------------------------------------
Index Only Scan using test_in_pkey on test_in (cost=0.15..2.17 rows=1 width=4)
Index Cond: (id = 1)
(2 rows)
Notice that the plan is the same, so is the index condition, this ensures that the same cost is not just accidental. During the query rewriting stage of the execution PostgreSQL detects some simple cases of equivalency and rewrites them in a canonical form.
To see another example of query that is still semantically equivalent but is not rewritten to the exact same plan you could try:
test=# explain select * from test_in where id in ('1', '1');
QUERY PLAN
---------------------------------------------------------------------------------
Index Only Scan using test_in_pkey on test_in (cost=0.15..3.34 rows=2 width=4)
Index Cond: (id = ANY ('{1,1}'::integer[]))
(2 rows)
MySQL OR vs IN performance
The accepted answer doesn't explain the reason.
Below are quoted from High Performance MySQL, 3rd Edition.
In many database servers, IN() is just a synonym for multiple OR clauses, because the two are logically equivalent. Not so in MySQL, which sorts the values in the IN() list and uses a fast binary search to see whether a value is in the list. This is O(Log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) in the size of the list (i.e., much slower for large lists)
Equals(=) vs. LIKE
Different Operators
LIKE
and =
are different operators. Most answers here focus on the wildcard support, which is not the only difference between these operators!
=
is a comparison operator that operates on numbers and strings. When comparing strings, the comparison operator compares whole strings.
LIKE
is a string operator that compares character by character.
To complicate matters, both operators use a collation which can have important effects on the result of the comparison.
Motivating Example
Let us first identify an example where these operators produce obviously different results. Allow me to quote from the MySQL manual:
Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:
mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
| 1 |
+--------------------------------------+
Please note that this page of the MySQL manual is called String Comparison Functions, and =
is not discussed, which implies that =
is not strictly a string comparison function.
How Does =
Work?
The SQL Standard § 8.2 describes how =
compares strings:
The comparison of two character strings is determined as follows:
a) If the length in characters of X is not equal to the length
in characters of Y, then the shorter string is effectively
replaced, for the purposes of comparison, with a copy of
itself that has been extended to the length of the longer
string by concatenation on the right of one or more pad
characters, where the pad character is chosen based on CS. If
CS has the NO PAD attribute, then the pad character is an
implementation-dependent character different from any
character in the character set of X and Y that collates less
than any string under CS. Otherwise, the pad character is a
<space>.b) The result of the comparison of X and Y is given by the
collating sequence CS.c) Depending on the collating sequence, two strings may
compare as equal even if they are of different lengths or
contain different sequences of characters. When the operations
MAX, MIN, DISTINCT, references to a grouping column, and the
UNION, EXCEPT, and INTERSECT operators refer to character
strings, the specific value selected by these operations from
a set of such equal values is implementation-dependent.
(Emphasis added.)
What does this mean? It means that when comparing strings, the =
operator is just a thin wrapper around the current collation. A collation is a library that has various rules for comparing strings. Here is an example of a binary collation from MySQL:
static int my_strnncoll_binary(const CHARSET_INFO *cs __attribute__((unused)),
const uchar *s, size_t slen,
const uchar *t, size_t tlen,
my_bool t_is_prefix)
{
size_t len= MY_MIN(slen,tlen);
int cmp= memcmp(s,t,len);
return cmp ? cmp : (int)((t_is_prefix ? len : slen) - tlen);
}
This particular collation happens to compare byte-by-byte (which is why it's called "binary" — it doesn't give any special meaning to strings). Other collations may provide more advanced comparisons.
For example, here is a UTF-8 collation that supports case-insensitive comparisons. The code is too long to paste here, but go to that link and read the body of my_strnncollsp_utf8mb4()
. This collation can process multiple bytes at a time and it can apply various transforms (such as case insensitive comparison). The =
operator is completely abstracted from the vagaries of the collation.
How Does LIKE
Work?
The SQL Standard § 8.5 describes how LIKE
compares strings:
The <predicate>
M LIKE P
is true if there exists a partitioning of M into substrings
such that:i) A substring of M is a sequence of 0 or more contiguous
<character representation>s of M and each <character
representation> of M is part of exactly one substring.ii) If the i-th substring specifier of P is an arbitrary
character specifier, the i-th substring of M is any single
<character representation>.iii) If the i-th substring specifier of P is an arbitrary string
specifier, then the i-th substring of M is any sequence of
0 or more <character representation>s.iv) If the i-th substring specifier of P is neither an
arbitrary character specifier nor an arbitrary string specifier,
then the i-th substring of M is equal to that substring
specifier according to the collating sequence of
the <like predicate>, without the appending of <space>
characters to M, and has the same length as that substring
specifier.v) The number of substrings of M is equal to the number of
substring specifiers of P.
(Emphasis added.)
This is pretty wordy, so let's break it down. Items ii and iii refer to the wildcards _
and %
, respectively. If P
does not contain any wildcards, then only item iv applies. This is the case of interest posed by the OP.
In this case, it compares each "substring" (individual characters) in M
against each substring in P
using the current collation.
Conclusions
The bottom line is that when comparing strings, =
compares the entire string while LIKE
compares one character at a time. Both comparisons use the current collation. This difference leads to different results in some cases, as evidenced in the first example in this post.
Which one should you use? Nobody can tell you that — you need to use the one that's correct for your use case. Don't prematurely optimize by switching comparison operators.
SQL 'like' vs '=' performance
See https://web.archive.org/web/20150209022016/http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx
Quote from there:
the rules for index usage with LIKE
are loosely like this:
If your filter criteria uses equals =
and the field is indexed, then most
likely it will use an INDEX/CLUSTERED
INDEX SEEKIf your filter criteria uses LIKE,
with no wildcards (like if you had a
parameter in a web report that COULD
have a % but you instead use the full
string), it is about as likely as #1
to use the index. The increased cost
is almost nothing.If your filter criteria uses LIKE, but
with a wildcard at the beginning (as
in Name0 LIKE '%UTER') it's much less
likely to use the index, but it still
may at least perform an INDEX SCAN on
a full or partial range of the index.HOWEVER, if your filter criteria uses
LIKE, but starts with a STRING FIRST
and has wildcards somewhere AFTER that
(as in Name0 LIKE 'COMP%ER'), then SQL
may just use an INDEX SEEK to quickly
find rows that have the same first
starting characters, and then look
through those rows for an exact match.
(Also keep in mind, the SQL engine
still might not use an index the way
you're expecting, depending on what
else is going on in your query and
what tables you're joining to. The
SQL engine reserves the right to
rewrite your query a little to get the
data in a way that it thinks is most
efficient and that may include an
INDEX SCAN instead of an INDEX SEEK)
What is the advantage of using the LIKE clause in mysql
If your column col
is index , then using the first query will use the index
SELECT col FROM table WHERE col='anything'
And in the 2nd case even if the column col
is indexed optimizer will not be able to use the index.
So the first query would be better in performance.
Check out more about query optimization
https://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html
MySQL IN operator performance on (large?) number of values
Generally speaking, if the IN
list gets too large (for some ill-defined value of 'too large' that is usually in the region of 100 or smaller), it becomes more efficient to use a join, creating a temporary table if need so be to hold the numbers.
If the numbers are a dense set (no gaps - which the sample data suggests), then you can do even better with WHERE id BETWEEN 300 AND 3000
.
However, presumably there are gaps in the set, at which point it may be better to go with the list of valid values after all (unless the gaps are relatively few in number, in which case you could use:
WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836
Or whatever the gaps are.
Related Topics
List and Download Clicked File from Ftp
PHP Access Network Path Under Windows
Inserting Google Calendar Entries with Service Account
Add Fee Based on Specific Payment Methods in Woocommerce
PHP Using Preg_Replace:"Delimiter Must Not Be Alphanumeric or Backslash" Error
Finding Common Prefix of Array of Strings
Logging in to Joomla 1.5 Using External Form (Not Within Joomla Folder, But on Same Server)
Convert a String into an Array of Characters
How to Set a Default Attribute Value for a Laravel/Eloquent Model
Why Is It Whenever I Use Scandir() I Receive Periods at the Beginning of the Array
Utf-8 Character Encoding Battles JSON_Encode()
Will Xpath 2.0 And/Or Xslt 2.0 Be Implemented in PHP
How to Change PHP's Eregi to Preg_Match