-- tested with MySQL 5.6 -- index is used for accessing entries starting with "ba" EXPLAIN SELECT * FROM history WHERE name LIKE 'ba%' LIMIT 10; -- index is used for accessing entries starting with "b". Get entries ending with "r" by filtering the "b" results EXPLAIN SELECT * FROM history WHERE name LIKE 'b%r' LIMIT 10; -- no index used EXPLAIN SELECT * FROM history WHERE name LIKE '%ar' LIMIT 10;
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | history | null | range | name_timestamp_idx,name_status_idx | name_timestamp_idx | 767 | null | 402 | 100 | Using index condition |
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | history | null | range | name_timestamp_idx,name_status_idx | name_timestamp_idx | 767 | null | 603 | 100 | Using index condition |
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | history | null | ALL | null | null | null | null | 1407 | 11.11 | Using where |