Mysql_ Prefix Index _ Build

--Check the frequency of occurrence
Select COUNT (*) as Cnt,city
From Sakila.city_demo GROUP by city ORDER by CNT DESC LIMIT 10;

1.select count (Distinct city)/count (*) from Sakila.city_demo;

* Selection of complete columns

2. Select COUNT (distinct left (city,3))/count (*) as SEL3,
COUNT (distinct left (city,4))/count (*) as SEL4,
COUNT (distinct left (city,5))/count (*) as SEL5,
COUNT (distinct left (city,6))/count (*) as SEL6,
COUNT (distinct left (city,7))/count (*) as Sel7

From Sakila.city_demo;

* The closer the prefix output is to the 1 output, the higher the performance

3. ALTER TABLE Sakilal.city_demo add key (city (7));

Usage scenarios:

The 16 binary unique ID uses the prefix index. Typically uses the first 8 bits, which significantly improves performance

Mysql_ Prefix Index _ Build