CREATE TABLE t1(a int, b VARCHAR(5), PRIMARY KEY(a))ENGINE=INNODB;
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	index	k2	k2	19	NULL	5	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	4	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	2	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	3	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	4	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	4	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	index	k2	k2	19	NULL	5	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	5	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	5	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	2	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	2	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	4	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	4	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	4	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	4	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	4	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))ENGINE=INNODB  DEFAULT CHARSET=latin1;
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	index	k2	k2	7	NULL	5	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	4	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	2	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	3	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	4	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	4	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	index	k2	k2	7	NULL	5	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	5	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	5	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	2	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	2	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	4	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	4	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	4	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	4	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	4	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))ENGINE=INNODB  DEFAULT CHARSET=utf32;
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	index	k2	k2	19	NULL	5	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	4	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	2	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	3	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	k2	k2	19	NULL	5	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	index	k2	k2	19	NULL	5	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	index	k2	k2	19	NULL	5	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 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	4	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	4	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	index	k2	k2	19	NULL	5	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	range	k2	k2	19	NULL	5	100.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	5	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	5	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	2	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	2	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	4	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	4	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	4	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	range	k2	k2	19	NULL	5	100.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	4	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	4	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))ENGINE=INNODB;
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	index	k2	k2	19	NULL	5	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	4	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	2	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	3	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	4	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	4	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	index	k2	k2	19	NULL	5	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	5	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	5	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	2	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	2	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	4	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	4	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	4	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	4	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	4	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))ENGINE=INNODB  DEFAULT CHARSET=utf32;
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	19	NULL	4	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.
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN SELECT /*+ INDEX(t1 k4) */ 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	k4	k4	4	NULL	2	16.67	Using where; Using index
Warnings:
Note	1003	/* select#1 */ select /*+ INDEX(`t1`@`select#1` `k4`) */ count(0) AS `COUNT(*)` from `test`.`t1` where ((`test`.`t1`.`a` > 4) and (`test`.`t1`.`b` like 'abc%'))
SELECT /*+ INDEX(t1 k4) */ COUNT(*) FROM t1 WHERE a > 4 AND b like 'abc%';
COUNT(*)
2
# 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	k3	11	NULL	5	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	2	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	k2	23	NULL	2	100.00	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 '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	2	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	2	100.00	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%'
1
1
# 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	15	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%'
1
1
# 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))ENGINE=INNODB;
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))) ENGINE=INNODB;
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	range	f1	f1	4	NULL	1	100.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;
#
# Bug#28934315 CASE STATEMENT USE IN CONDITIONAL WITH SUB_PART INDEX REGRESSION IN 8.0.
#
CREATE TABLE t1 (
id INT(11) NOT NULL AUTO_INCREMENT,
example VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY example_key (example(9))
) ENGINE=InnoDB;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE TABLE t2 (
id INT(11) NOT NULL AUTO_INCREMENT,
example VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1 (example) VALUES ('1234567890');
INSERT INTO t2 (example) VALUES ('1234567890');
EXPLAIN SELECT t2.example, t2.id FROM t2, t1 WHERE t1.example = LOWER(t2.example);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	t1	NULL	ref	example_key	example_key	38	func	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t2`.`example` AS `example`,`test`.`t2`.`id` AS `id` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`example` = lower(`test`.`t2`.`example`))
SELECT t2.example, t2.id FROM t2, t1 WHERE t1.example = LOWER(t2.example);
example	id
1234567890	1
DROP TABLE t1, t2;