MySQL index (1)

Indexes used in database development occupy a very important position. Good indexes will double the Read and Write efficiency of the database. Poor indexes will drag down the entire system and even lead to disasters.

There are three types of indexes:

Index ---- normal index, data can be repeated

Unique ---- unique index, which must be unique for all records

Primary Key ---- primary key index, that is, the corresponding column must be the primary key on the basis of the unique index.

The general statement for creating an index is as follows:

Alter table tb_name add index index_name (column_list) </P> <p> alter table tb_name add unique index_name (column_list) </P> <p> alter table tb_name add primary key index_name (column_list)MySQL uses indexes for <, <=, =,>,> =, between, in, and like statements not starting with %.

To verify the like processing method, the following is a simple test:

 

Create Table tb_test (fval varchar (50 ));
First, no indexes are added, 100 million records are inserted randomly, and the like query speed is tested.

 

<Textarea style = "width: 1029px; Height: 495px; "readonly name =" code "class =" SQL "> Create definer = 'root' @ 'localhost' procedure 'P _ teset '() <br/> begin <br/> declare v_val varchar (20); <br/> declare v_str varchar (20); <br/> declare V_ I int; <br/> declare V_j int; </P> <p> set v_str = 'abcdefghijklmnopqrstuvwxyz '; <br/> set V_ I = 0; <br/> set V_j = 0; </P> <p> while V_ I <1000000 DO <br/> set v_val = ''; </P> <p> while V_j <13 DO <br/> set v_val = Concat (v_val, substring (v_str, 1, floor (1 + rand () * 26); <br/> set V_j = V_j + 1; <br/> end while; <br/> set V_j = 0; <br/> insert into tb_test (fval) <br/> values (v_val); </P> <p> set V_ I = V_ I + 1; <br/> end while; <br/> end $ </P> <p> delimiter; </textarea>Use % to test the like query speed:

 

 

Select * <br/> from tb_test <br/> where fval like '% AB'; <br/>70231 seconds

 

 

Select * <br/> from tb_test <br/> where fval like 'AB %'961406 seconds

 

Add the following index:

 

Alter table tb_test add index my_index (fval );
Test the like query speed again:

 

Select * <br/> from tb_test <br/> where fval like '% AB ';70231 seconds

Select * <br/> from tb_test <br/> where fval like 'AB %'961406 seconds

The table is in MyISAM format. We can see that the query efficiency has not been improved after the index is added. Use the explain keyword to view the running status of like 'AB %:

Explain
Select *
From tb_test ignore index (my_index)
Where fval like '% AB ';

Explain select *
From tb_test
Where fval like '% AB ';