Prefix index and index selectivity of MySQL Indexes

Sometimes a long character column needs to be indexed, which will make the index larger and slower. One policy is to simulate hash indexes. But sometimes this is not good enough, then?

Generally, you can index the first few characters instead of all values to save space and improve performance. This reduces the size required for the index, but also reduces the selectivity. The index selectivity is the ratio of non-repeated index values to all rows in the table. A highly selective index is advantageous because it enables mysql to filter out more rows during search matching. The selection rate of a unique index is 1, which is the best value.

If you want to index BLOG and TEXT columns, or long varchar columns, you must define a prefix index because mysql does not allow you to index their full culture.

You can calculate many different prefix lengths in the same query and select a better choice. [Help house http://www.bkjia.com]

(Use the LEFT function, left (city, 4 ))

Add prefix Index

Mysql> alter table test. test add key (city (7 ));

Prefix indexes can greatly reduce the index size and increase the speed. However, mysql cannot use prefix indexes in order by and group by queries, nor overwrite indexes with primary keys.

Sometimes suffix indexes are useful, such as searching all email addresses of a domain name. Mysql does not support reverse indexing, but it can store reverse strings and index their prefixes. You can use triggers to maintain such indexes.