Shulou(Shulou.com)06/01 Report--

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 |

+-- +

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.