Pattern matching like'%XXX%' optimization
In MySQL, like'XXX% can use indexes, but like'% XXX%' can't, for example, in this case:
View the number of test table rows:
Click (here) to collapse or open
Mysql > select count (*) from test03
+-+
| | count (*) |
+-+
| | 117584 |
Comparison of +-+ two like matches:
Click (here) to collapse or open
Mysql > explain select count (*) from test03 where username like'1%'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | test03 | range | idx_test03_name | idx_test03_name | 302 | NULL | 58250 | Using where; Using index |
+-+-
1 row in set (0.03 sec)
Mysql > explain select count (*) from test03 where username like'% 1%'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | test03 | index | NULL | idx_test03_name | 302 | NULL | 116500 | Using where; Using index |
+-+-
1 row in set (0.00 sec) optimization idea:
In this test table, id is the primary key, and the data is saved on the leaf node, so you can go to the id column of select from the index without having to read the data rows (only the select field happens to be the index, then the overlay index is used). By overwriting the index, reduce IGO and improve performance.
Optimize the previous execution plan:
Click (here) to collapse or open
Mysql > explain select count (*) from test03 where username like'% 1%'
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | test03 | ALL | NULL | NULL | NULL | NULL | 7164 | Using where |
+-+ execution plan after optimization:
Click (here) to collapse or open
Mysql > explain select count (*) from test03 a join (select id from test03 where username like'% 1%') b on a.id=b.id
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | PRIMAR | | ALL | NULL | NULL | NULL | NULL | 7164 | NULL |
| | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 8 | b.id | 1 | Using index |
| | 2 | DERIVED | test03 | ALL | NULL | NULL | NULL | NULL | 7164 | Using where |
+-- +
