High performance indexing-high performance indexing strategy

1. Stand-alone columns

If the columns in the query are not independent, then MySQL does not use the index. A "stand-alone column" means that an indexed column cannot be part of an expression or a parameter to a function.

2. Prefix index and index selectivity

　For very long character columns, you can index the beginning of some characters, which can greatly save the index space, thus improving the efficiency of the index. However, the selectivity of the index is reduced. The selectivity of an index is the ratio of non-repeating index values (also known as cardinality) to the total number of records in the data table. The higher the selectivity of the index, the higher the query efficiency. The trick is to choose a long enough prefix to ensure high selectivity, but not too long (to save space). The prefix index is an efficient way to make indexes smaller and faster, but on the other hand: MySQL cannot use the prefix index for order by and group by, nor can it use the prefix index for overwrite scans.

High performance indexing-high performance indexing strategy