810 lines
50 KiB
Plaintext
810 lines
50 KiB
Plaintext
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;
|