The problem and example of string indexing in MySQL

The cause of the event is the online log found on the MySQL slow query. 1 million data volume standards, the joint query all Go index case, it is more than 600 milliseconds. Puzzled, but the data was raised 30 times times after the index column was changed from varchar (50) to bigint type. The reason for this is that there are a lot of comparison operations to search on the index tree, and string comparisons are more time-consuming than integers.

Therefore, it is recommended that you do not index the string column in general, and if you do not want to use a string index, you can use the following two methods:

1. Only the leftmost n characters of a string are indexed and recommended n<=10; such as index left (address,8), but it is necessary to know that the prefix index cannot be used in an order by or on an index overlay.

2. Use the hash method on the string to convert the string to an integer, Address_key=hashtoint (address), Address_key indexed, query can be used as follows query where Address_key = Hashtoint (' Beijing,china ') and address = ' Beijing,china ';

Example

I found an issue in the following statement:

SELECT * from region where city like "Renqiu%" limit 10;
SELECT * from region where city like "Shangqiu%" limit 10;

The results of these two statements are the same,

A similar situation is as follows:

SELECT * from region where city like "Linan%" limit 10;
SELECT * from region where city like "Liu'an%" limit 10;

Linhai Longhai
Zaoyang Yiyang City
Wenzhou Wuzhou