Which is faster INSTR vs Like Prefix For varchar In MYSQL-CodePudding

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

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%'

CodePudding user response：

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.

CodePudding user response：

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