Which is faster INSTR vs Like Prefix For varchar In MYSQL

Question

If my goal is to find out if there is a string in the column. The column has no unique btree index. Which is faster and more efficient: INSTR vs LIKE prefix for varchar in MYSQL, and why?

Or are there other more-efficient methods?

INSTR(column, 'value') > 0

vs

column LIKE 'value%'

I looked up several questions, but there were only questions and answers about wild cards front and back.

For example,

column LIKE '%value%'

@GSerg That won't help him, because it's using a leading wildcard and the question specifically asked about matches without the leading wildcard. — Joel Coehoorn, Jul 25 '22 at 15:12
@JoelCoehoorn The duplicate [covers that too](https://stackoverflow.com/a/2451691/11683). And the similar performance otherwise. And the advice to use fulltext. — GSerg, Jul 25 '22 at 15:15

Joel Coehoorn · Accepted Answer · 2022-07-25T15:08:08.413

They are not the same.

column like 'value%' is a starts with match, equivalent to INSTR(column, 'value') = 1, rather than INSTR(column, 'value') > 0.

On the other hand, INSTR(column, 'value') > 0 is a contains anywhere match, equivalent to column LIKE '%value%' instead of column LIKE 'value%'.

Of these four expressions, column LIKE 'value%' is likely to perform the best, because it's the only one that still has a chance of using any index for the column.

But it sounds like you want the contains anywhere match, and there's probably not any meaningful difference between column like '%value%' and INSTR(column, 'value') > 0. The best option here is likely a full-text search.

score 0 · Answer 2 · answered Jul 25 '22 at 15:23

A simple test on my test table (integers) shows that LIKE is faster.

MySQL [test]> select * from integers where instr(t2,'A')>0;
+----+--------------------------------------+----------+------+
| i  | t1                                   | f        | t2   |
+----+--------------------------------------+----------+------+
| 42 | 8f0c8b96-aa60-11eb-aa31-309c23b7280c | 0.983418 | ABC  |
+----+--------------------------------------+----------+------+
1 row in set (24.08 sec)

MySQL [test]> select * from integers where instr(t2,'A')>0;
+----+--------------------------------------+----------+------+
| i  | t1                                   | f        | t2   |
+----+--------------------------------------+----------+------+
| 42 | 8f0c8b96-aa60-11eb-aa31-309c23b7280c | 0.983418 | ABC  |
+----+--------------------------------------+----------+------+
1 row in set (24.11 sec)

MySQL [test]> explain select * from integers where instr(t2,'A')>0;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | integers | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2104867 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

MySQL [test]> select * from integers where t2 like 'A%';
+----+--------------------------------------+----------+------+
| i  | t1                                   | f        | t2   |
+----+--------------------------------------+----------+------+
| 42 | 8f0c8b96-aa60-11eb-aa31-309c23b7280c | 0.983418 | ABC  |
+----+--------------------------------------+----------+------+
1 row in set (1.00 sec)

MySQL [test]> select * from integers where t2 like 'A%';
+----+--------------------------------------+----------+------+
| i  | t1                                   | f        | t2   |
+----+--------------------------------------+----------+------+
| 42 | 8f0c8b96-aa60-11eb-aa31-309c23b7280c | 0.983418 | ABC  |
+----+--------------------------------------+----------+------+
1 row in set (1.00 sec)

MySQL [test]> explain select * from integers where t2 like 'A%';
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | integers | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2104867 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

This table has 2621442 records, in MySQL 8.0.29, with this DDL:

CREATE TABLE `integers` (
  `i` int NOT NULL,
  `t1` varchar(36) DEFAULT NULL,
  `f` float DEFAULT NULL,
  `t2` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`i`),
  KEY `integers_t1` (`t1`),
  KEY `idx_f` (`f`),
  KEY `even` (((`i` % 2)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_AUTO_RECALC=1

Which is faster INSTR vs Like Prefix For varchar In MYSQL

2 Answers2