MySQL set the appropriate index length

The ideal index:

1. Tables with very frequent table queries are indexed relative to write operations

2. High field sensitivity

3. The length is small (suitable length, not the smaller the better)

4. Try to cover characters commonly used segment

These conditions can be combined to achieve the optimal index, this time we focus on the establishment of a suitable length index, the length of the index directly affect the size of the index file, so it will affect the speed of adding and deleting changes

Set the Length field for a field of character type query when the sensitivity is high, if the field is just set up a query a lot of similar match degree is not high, the length is right, otherwise too long index file will be large, so

Make a balance between the degree and the length.

1. Take a look at the query that does not set the index

Mysql> explain select Id,title from B2b_goods where title= "test commodity";

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

| 1 | Simple | B2b_goods | All | NULL | NULL | NULL | NULL | 5061 | Using where |

+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+

1 row in Set (0.00 sec)

Summary: found that the statement did not use the index, scanned 5,061 data

2. Create an index to the title field

Mysql> ALTER TABLE B2b_goods Add index index_title (' title ');

Query OK, 0 rows affected (0.19 sec)

records:0 duplicates:0 warnings:0

Mysql> explain select Id,title from B2b_goods where title= "test commodity";

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+----------------- ---------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+----------------- ---------+

| 1 | Simple | B2b_goods | Ref | Index_title | Index_title | 150 |    Const | 1 | Using where; Using Index |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+----------------- ---------+

1 row in Set (0.00 sec)

Summary: It is found that the number of scan bars in this statement has been reduced to one, but it is true that the index length (key_len) is too long to be compared with memory when updating.

3. Set a high-sensitivity and length-appropriate index

Habits of the algorithm:

Select COUNT (distinct left (' title ', num))/count (*) from B2b_goods;

Here num refers to the length of the interception, in fact, you can also find the length of the query, the larger the ratio is better

(1). Set 6 Lengths

Mysql> Select COUNT (distinct left (' title ', 6))/count (*) from B2b_goods;

+------------------------------------------+

| COUNT (distinct left (' title ', 6))/count (*) |

+------------------------------------------+

| 0.7718 |

+------------------------------------------+

1 row in Set (0.01 sec)

(2). Set 13 Lengths

Mysql> Select COUNT (distinct left (' title ',))/count (*) from B2b_goods;

+-------------------------------------------+

| COUNT (distinct left (' title ',))/count (*) |

+-------------------------------------------+

| 0.8288 |

+-------------------------------------------+

1 row in Set (0.01 sec)

(3). Set 25 Lengths

Mysql> Select COUNT (distinct left (' title ')]/count (*) from B2b_goods;

+-------------------------------------------+

| COUNT (distinct left (' title '))/count (*) |

+-------------------------------------------+

| 0.8562 |

+-------------------------------------------+

1 row in Set (0.01 sec)

(4). Set 30 Lengths

Mysql> Select COUNT (distinct left (' title ', ")"/count (*) from B2b_goods;

+-------------------------------------------+

| COUNT (distinct left (' title ', ') ')/count (*) |

+-------------------------------------------+

| 0.8573 |

+-------------------------------------------+

1 row in Set (0.01 sec)

(5). Set 35 Lengths

Mysql> Select COUNT (distinct left (' title ', ")")/count (*) from B2b_goods;

+-------------------------------------------+

| COUNT (distinct left (' title ', ') ')/count (*) |

+-------------------------------------------+

| 0.8573 |

+-------------------------------------------+

1 row in Set (0.01 sec)

Summary: found that the length of the setting field is beginning to increase when the matching degree is high, to a certain value changes in the area is gentle, found that intercept 30 and 35 no distinction, sum up and maintain the appropriate length
We create an index with a length of 25

4. Create an index with a high degree of sensitivity and a moderate length

ALTER TABLE b2b_goods DROP INDEX index_title;

ALTER TABLE B2b_goods Add index index_title (' title ' (25));

Mysql> explain select Id,title from B2b_goods where title= "test commodity";

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+

| 1 | Simple | B2b_goods | Ref | Index_title | Index_title | 75 |    Const | 1 | Using where |

+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+

1 row in Set (0.00 sec)

Summary: Compared to the previous set length of the comparison Key_len from 150 to 75, the number of scan bar is still one, in contrast, this length is excellent index length.

MySQL set the appropriate index length