Mysql prefix Index

Mysql prefix index Application Scenario: There is an address field in the database, the type is varchar (100), the business determines the need to often based on
Address. Determine the Selectivity of www.2cto.com: SQL code SELECT count (DISTINCT (address)/count (*) AS selecti.pdf FROM info; + ------------- + | selecti.pdf | + ------------- + | 0.8745 | + ------------- + <address> the selection is good, but the length is 100. It is obviously inappropriate to create an index for the entire field. You can consider creating an index.
Prefix index, for example, <left (address, 5)> to check its selectivity: www.2cto.com SQL code SELECT count (DISTINCT (left (address, 5)/count (*) AS selecti.pdf FROM info; + ------------- + | selecti.pdf | + --------------- + | 0.5981 | + ------------- + the Selectivity is good, but it is too low compared with 0.8745, so we can increase the prefix length to 10.
Optional: www.2cto.com SQL code SELECT count (DISTINCT (left (address, 10)/count (*) AS selecti.pdf FROM info; + ------------- + | selecti.pdf | + ------------- + | 0.8239 | + ------------- + and 0.8745 are very close, but the index length is only 10, so you can decide to create a prefix index. Prefix indexes take into account both the index size and query speed, but the disadvantage is that they cannot be used for order by and GROUP
And cannot be used for Covering index (that is, when the index itself contains all the data required for the query
Data File itself ).