LIKE performance including wildcards (%)
If I execute this query:
SELECT * FROM table1 WHERE name LIKE '%girl%'
It returns all records where the name contains "girl". However, due to the first pattern %
in the status, LIKE
it cannot (or does not use) indexes as stated here: Mysql Improve search performance using wildcards (%%)
Then I changed the request to:
SELECT * FROM table1 WHERE name LIKE 'girl%' OR name LIKE '%girl%'
On the left side, OR
I've removed the template so that it can use indices. But the performance gain depends on how MySQL evaluates the query.
Hence my question: Does the performance of my query increase when an operator is added OR
?
source to share
No, the performance will be the same. MySQL still has to evaluate the first condition ( LIKE '%girl%'
) because of OR
. Then he can evaluate the second condition using the index. You can see this information when EXPLAIN
your query (mysql will show that it still needs to do a full table scan, which means checking every row):
EXPLAIN SELECT * FROM table1 WHERE name LIKE 'girl%' OR name LIKE '%girl%'
To improve performance for these types of queries, you will need to use full-text indexes and special syntax to query them. But FT indices behave differently and are not suitable for everything.
source to share
(This answer contains a summary of the comments and also contradicts some of the previous notes.)
Leading template :
SELECT * FROM table1 WHERE name LIKE 'girl%' OR name LIKE '%girl%'
SELECT * FROM table1 WHERE name LIKE '%girl%'
Either will scan the table and ignore any indexes. This is both because of the leading wild card and because of OR
. (It won't use an index for "girl%", contrary to what @ Marki555 says - it's not worth the extra effort.)
Range query via LIKE (no leading pattern):
SELECT * FROM table1 WHERE name LIKE 'girl%'
would probably use INDEX(name)
like this:
- Expand the BTree for this index to the first
name
one starting with "girl"; - Scan forward (in index) to the last line starting with "Girl";
- For each item in step 2, go to data to get
*
.
Since step 3 can be expensive, the optimizer first estimates how many rows will need to be touched in step 2. If more than 20% (approximate) of the table, it will fall back to scanning the table. (Hence my use is "possible".)
"Coverage Index" :
SELECT name FROM table1 WHERE name LIKE '%girl%'
This will always use INDEX(name)
. This is because the index "covers". That is, all columns in SELECT
are in INDEX
. Since INDEX
it looks and looks like a table, an index scan is the best way to make a query. Since the index is usually smaller than the table, index scans are usually faster than table scans.
Here's a less obvious "coverage index", but only applicable to InnoDB:
PRIMARY KEY(id)
INDEX(name)
SELECT id FROM table1 WHERE name LIKE '%girl%'
Every secondary key (name)
in InnoDB implicitly includes a PK (id)
. Hence the index looks like (name, id)
. Hence, all columns in SELECT
are in the index. Hence, this is the "coverage index". Hence, it will use the index and perform an "index scan".
A "coverage index" is indicated by the symbol Using index
displayed in EXPLAIN SELECT ...
.
source to share