An example of pattern matching like & #39; % xxx % & #39; Optimization

Indexes can be used for like 'xxx % 'in MySQL, but like' % xxx % 'cannot.

 
 

  
  
mysql> desc artist; 
  
  
+------------+-----------------------------------------------+------+-----+---------+-------+ 
  
  
| Field      | Type                                          | Null | Key | Default | Extra | 
  
  
+------------+-----------------------------------------------+------+-----+---------+-------+ 
  
  
| artist_id  | int(10) unsigned                              | NO   | PRI | NULL    |       | 
  
  
| type       | enum('Band','Person','Unknown','Combination') | NO   |     | NULL    |       | 
  
  
| name       | varchar(255)                                  | NO   | MUL | NULL    |       | 
  
  
| gender     | enum('Male','Female')                         | YES  |     | NULL    |       | 
  
  
| founded    | year(4)                                       | YES  |     | NULL    |       | 
  
  
| country_id | smallint(5) unsigned                          | YES  |     | NULL    |       | 
  
  
+------------+-----------------------------------------------+------+-----+---------+-------+ 
  
  
6 rows in set (0.58 sec) 
 
 

For example:

 
 

  
  
mysql> explain select * from artist where name like '%Queen%';           
  
  
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ 
  
  
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       | 
  
  
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ 
  
  
|  1 | SIMPLE      | artist | ALL  | NULL          | NULL | NULL    | NULL | 589410 | Using where | 
  
  
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ 
  
  
1 row in set (0.02 sec) 
 
 

Now we can further optimize it by overwriting the index:

 
 

  
  
mysql> explain select artist_id from artist where name like '%Queen%'; 
  
  
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+ 
  
  
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    | 
  
  
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+ 
  
  
|  1 | SIMPLE      | artist | index | NULL          | name | 257     | NULL | 589410 | Using where; Using index | 
  
  
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+ 
  
  
1 row in set (0.03 sec) 
 
 

Here, artist_id is a primary key clustered index), and the InnoDB Engine stores data on the leaf node). The select artist_id column can be obtained from the index, you do not need to read data rows. As long as your SELECT field is an index, you can use the overwrite index.) overwriting indexes can reduce IO and improve performance.

Overwrite the index simply: I want to find a content in the book, but I wrote this directory in detail and I got it in the directory. I don't need to go to this page to view it.

Previous SQL Execution time:

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1T33141I-0.jpg "/>

Optimized SQL Execution time:

650) this. width = 650; "border =" 0 "alt =" "src =" http://www.bkjia.com/uploads/allimg/131229/1T3313635-1.jpg "/>

 

This article is from the "hechun's technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/1112687