CREATE TABLE t1(a int, b VARCHAR(5), PRIMARY KEY(a));
INSERT INTO t1 VALUES (1, 'a'), (2, 'ab'), (3, 'abc'), (4, 'abcd'), (5, 'abcde');
ALTER TABLE t1 ADD KEY k2 (b(4));
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
# 'Using index' method is used since wild string is suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcd%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcd%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abc\%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abc\\%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\%abc%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\%abc%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abc%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abc%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	19	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	19	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '____%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	19	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '____%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\_\_\_\_%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\_\\_\\_\\_%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\%\%\%\%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\%\\%\\%\\%')
EXPLAIN SELECT b LIKE 'abcd' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abcd') AS `b LIKE 'abcd'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE 'abc%' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abc%') AS `b LIKE 'abc%'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\\\')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\\\%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\\\%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a_';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a_')
# 'Using index' method is not used  since wild string is not suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%a')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%c%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%c%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%c';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%c')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcde%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcde%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcde%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcde%')
EXPLAIN SELECT b FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE 'abcde' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abcde') AS `b LIKE 'abcde'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE '%bc' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like '%bc') AS `b LIKE '%bc'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaaa\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaaa\\')
# 'Using index' method is not used  since wild string is not suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%' OR b like '%a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	k2	NULL	NULL	NULL	5	36.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` like 'a%') or (`test`.`t1`.`b` like '%a'))
# 'Using index' method is used since wild string is suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
# 'Using index' method is not used  since 'b' field is used in select output.
EXPLAIN SELECT b FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
# Index k2 shouldn't be used.
EXPLAIN SELECT COUNT(*) FROM t1 IGNORE INDEX(k2) WHERE b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` IGNORE INDEX (`k2`) where (`test`.`t1`.`b` like 'a%')
DROP TABLE t1;
CREATE TABLE t1(a int, b VARCHAR(5), PRIMARY KEY(a)) DEFAULT CHARSET=latin1, COLLATE latin1_bin;
INSERT INTO t1 VALUES (1, 'a'), (2, 'ab'), (3, 'abc'), (4, 'abcd'), (5, 'abcde');
ALTER TABLE t1 ADD KEY k2 (b(4));
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
# 'Using index' method is used since wild string is suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcd%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcd%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abc\%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abc\\%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\%abc%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\%abc%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abc%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abc%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	7	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	7	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '____%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	7	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '____%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\_\_\_\_%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\_\\_\\_\\_%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\%\%\%\%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\%\\%\\%\\%')
EXPLAIN SELECT b LIKE 'abcd' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abcd') AS `b LIKE 'abcd'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE 'abc%' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abc%') AS `b LIKE 'abc%'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\\\')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\\\%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\\\%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a_';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a_')
# 'Using index' method is not used  since wild string is not suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%a')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%c%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%c%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%c';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%c')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcde%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcde%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcde%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcde%')
EXPLAIN SELECT b FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE 'abcde' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abcde') AS `b LIKE 'abcde'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE '%bc' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like '%bc') AS `b LIKE '%bc'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaaa\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaaa\\')
# 'Using index' method is not used  since wild string is not suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%' OR b like '%a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	k2	NULL	NULL	NULL	5	36.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` like 'a%') or (`test`.`t1`.`b` like '%a'))
# 'Using index' method is used since wild string is suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
# 'Using index' method is not used  since 'b' field is used in select output.
EXPLAIN SELECT b FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	7	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
# Index k2 shouldn't be used.
EXPLAIN SELECT COUNT(*) FROM t1 IGNORE INDEX(k2) WHERE b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` IGNORE INDEX (`k2`) where (`test`.`t1`.`b` like 'a%')
DROP TABLE t1;
CREATE TABLE t1(a int, b VARCHAR(5), PRIMARY KEY(a)) DEFAULT CHARSET=utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
INSERT INTO t1 VALUES (1, 'a'), (2, 'ab'), (3, 'abc'), (4, 'abcd'), (5, 'abcde');
ALTER TABLE t1 ADD KEY k2 (b(4));
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
# 'Using index' method is used since wild string is suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcd%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcd%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abc\%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abc\\%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\%abc%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\%abc%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abc%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abc%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	15	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	15	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '____%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	15	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '____%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\_\_\_\_%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\_\\_\\_\\_%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\%\%\%\%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\%\\%\\%\\%')
EXPLAIN SELECT b LIKE 'abcd' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abcd') AS `b LIKE 'abcd'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE 'abc%' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abc%') AS `b LIKE 'abc%'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\\\')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\\\%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\\\%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a_';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a_')
# 'Using index' method is not used  since wild string is not suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%a')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%c%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%c%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%c';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%c')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcde%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcde%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcde%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcde%')
EXPLAIN SELECT b FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE 'abcde' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abcde') AS `b LIKE 'abcde'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE '%bc' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like '%bc') AS `b LIKE '%bc'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaaa\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaaa\\')
# 'Using index' method is not used  since wild string is not suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%' OR b like '%a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	k2	NULL	NULL	NULL	5	36.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` like 'a%') or (`test`.`t1`.`b` like '%a'))
# 'Using index' method is used since wild string is suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
# 'Using index' method is not used  since 'b' field is used in select output.
EXPLAIN SELECT b FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	15	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
# Index k2 shouldn't be used.
EXPLAIN SELECT COUNT(*) FROM t1 IGNORE INDEX(k2) WHERE b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` IGNORE INDEX (`k2`) where (`test`.`t1`.`b` like 'a%')
DROP TABLE t1;
CREATE TABLE t1(a int, b TEXT, PRIMARY KEY(a));
INSERT INTO t1 VALUES (1, 'a'), (2, 'ab'), (3, 'abc'), (4, 'abcd'), (5, 'abcde');
ALTER TABLE t1 ADD KEY k2 (b(4));
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
# 'Using index' method is used since wild string is suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcd%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcd%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abc\%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abc\\%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\%abc%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\%abc%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abc%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abc%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	19	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	19	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '____%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	k2	19	NULL	5	20.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '____%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\_\_\_\_%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\_\\_\\_\\_%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '\%\%\%\%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '\\%\\%\\%\\%')
EXPLAIN SELECT b LIKE 'abcd' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abcd') AS `b LIKE 'abcd'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE 'abc%' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abc%') AS `b LIKE 'abc%'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\\\')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaa\\\\%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaa\\\\%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a_';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a_')
# 'Using index' method is not used  since wild string is not suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like '%a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like '%a')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%c%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%c%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%c';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'a%c')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcde%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcde%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abcde%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abcde%')
EXPLAIN SELECT b FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE 'abcde' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abcde') AS `b LIKE 'abcde'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT b LIKE '%bc' FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like '%bc') AS `b LIKE '%bc'` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaaa\\';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaaa\\')
# 'Using index' method is not used  since wild string is not suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'a%' OR b like '%a';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	k2	NULL	NULL	NULL	5	36.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`b` like 'a%') or (`test`.`t1`.`b` like '%a'))
# 'Using index' method is used since wild string is suitable for the use with prefix key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
# 'Using index' method is not used  since 'b' field is used in select output.
EXPLAIN SELECT b FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
# Index k2 shouldn't be used.
EXPLAIN SELECT COUNT(*) FROM t1 IGNORE INDEX(k2) WHERE b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	5	20.00	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` IGNORE INDEX (`k2`) where (`test`.`t1`.`b` like 'a%')
DROP TABLE t1;
CREATE TABLE t1(a int, b VARCHAR(5), PRIMARY KEY(a)) DEFAULT CHARSET=utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
INSERT INTO t1 VALUES (1, 'a'), (2, 'ab'), (3, 'abc'), (4, 'abcd'), (5, 'abcde'), (6, 'abcdf');
ALTER TABLE t1 ADD KEY k2 (b(4));
ALTER TABLE t1 ADD KEY k3 (b(2));
ALTER TABLE t1 ADD KEY k4 (a, b(3));
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
# k2 index used since it's  covering.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'abc%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2,k3	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'abc%')
SELECT COUNT(*) FROM t1 WHERE b like 'abc%';
COUNT(*)
4
# k4 index used since it's  covering.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a > 4 AND b like 'abc%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,k2,k3,k4	k4	4	NULL	1	16.67	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`a` > 4) and (`test`.`t1`.`b` like 'abc%'))
SELECT COUNT(*) FROM t1 WHERE a > 4 AND b like 'abc%';
COUNT(*)
0
# k3 index is used since it's shorters key.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2,k3	k2	15	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'ab%')
SELECT COUNT(*) FROM t1 WHERE b like 'ab%';
COUNT(*)
5
# Index access is not used, no covering keys.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a > 4 AND b like 'abcde%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,k2,k3,k4	PRIMARY	4	NULL	1	16.67	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`a` > 4) and (`test`.`t1`.`b` like 'abcde%'))
SELECT COUNT(*) FROM t1 WHERE a > 4 AND b like 'abcde%';
COUNT(*)
1
# k2 index used since it's  covering.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE a > 4 AND b like 'abcd%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,k2,k3,k4	PRIMARY	4	NULL	1	16.67	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`a` > 4) and (`test`.`t1`.`b` like 'abcd%'))
SELECT COUNT(*) FROM t1 WHERE a > 4 AND b like 'abcd%';
COUNT(*)
2
# Index access is not used, no covering keys.
EXPLAIN SELECT b like 'abcdf%'  FROM t1 WHERE a > 4 AND b like 'abcd%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,k2,k3,k4	PRIMARY	4	NULL	1	16.67	Using where
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'abcdf%') AS `b like 'abcdf%'` from `test`.`t1` where ((`test`.`t1`.`a` > 4) and (`test`.`t1`.`b` like 'abcd%'))
SELECT b like 'abcdf%'  FROM t1 WHERE a > 4 AND b like 'abcd%';
b like 'abcdf%'
0
1
# Index access is used since k4 is covering.
EXPLAIN SELECT b like 'ab%'  FROM t1 WHERE a > 4 AND b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	PRIMARY,k2,k3,k4	k4	4	NULL	1	16.67	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'ab%') AS `b like 'ab%'` from `test`.`t1` where ((`test`.`t1`.`a` > 4) and (`test`.`t1`.`b` like 'a%'))
SELECT b like 'ab%'  FROM t1 WHERE a > 4 AND b like 'a%';
b like 'ab%'
# Index access is used since k3 is forced and covering.
EXPLAIN SELECT b like 'ab%'  FROM t1 FORCE INDEX(k3) WHERE a > 4 AND b like 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k3	k3	13	NULL	1	33.33	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`b` like 'ab%') AS `b like 'ab%'` from `test`.`t1` FORCE INDEX (`k3`) where ((`test`.`t1`.`a` > 4) and (`test`.`t1`.`b` like 'a%'))
SELECT b like 'ab%'  FROM t1 FORCE INDEX(k3) WHERE a > 4 AND b like 'a%';
b like 'ab%'
0
0
# No index access is used since second argument is not const.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like a;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	6	16.67	Using where
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like `test`.`t1`.`a`)
SELECT COUNT(*) FROM t1 WHERE b like a;
COUNT(*)
0
DROP TABLE t1;
CREATE TABLE t1(a int, b TEXT, c TEXT, PRIMARY KEY(a));
INSERT INTO t1 VALUES (1, 'a', 'a'), (2, 'ab', 'ab'), (3, 'abc', 'abc'), (4, 'abcd', 'abcd'), (5, 'abcde', 'abcde');
ALTER TABLE t1 ADD KEY k2 (b(4), c(3));
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
# Index access is used since key is covering.
EXPLAIN SELECT COUNT(*) FROM t1 WHERE b like 'aaaa';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	k2	k2	19	NULL	1	100.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`b` like 'aaaa')
DROP TABLE t1;
#
# Bug#27660560 RECENT REGRESSION: CRASH IN CHECK_COVERING_PREFIX_KEYS.
#
CREATE TABLE t1(f1 BLOB, KEY(f1(1))) ;
INSERT INTO t1 VALUES ('ccc'), ('aa');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SELECT (f1 LIKE null) from t1;
(f1 LIKE null)
NULL
NULL
SELECT 1 FROM t1 WHERE f1 NOT LIKE json_merge('' ,'+' );
ERROR 22032: Invalid JSON text in argument 1 to function json_merge_preserve: "The document is empty." at position 0.
SELECT 1 FROM t1 WHERE f1 LIKE json_contains('key2' ,'key4' );
ERROR 22032: Invalid JSON text in argument 1 to function json_contains: "Invalid value." at position 0.
SELECT 1 FROM t1 WHERE f1 LIKE json_depth(null);
1
EXPLAIN SELECT (f1 LIKE null) from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	NULL	f1	4	NULL	2	100.00	Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`f1` like NULL) AS `(f1 LIKE null)` from `test`.`t1`
EXPLAIN SELECT (f1 LIKE null) from t1 WHERE f1 LIKE 'a%';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	index	f1	f1	4	NULL	2	50.00	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select (`test`.`t1`.`f1` like NULL) AS `(f1 LIKE null)` from `test`.`t1` where (`test`.`t1`.`f1` like 'a%')
DROP TABLE t1;