MySQL index type

MYSQL The index is divided into the following types:

• KEY or INDEX :

  A normal index is a generic index that differs from a unique index, where the column can contain the same value, so the rows in the index may contain exactly the same rows. These indexes do not impose any restrictions on the data, just to speed up the query operation of the data. ）

• unique  :

  The unique index value is that all the rows in the index (built on some columns) must be unique, meaning that the values of the fields in a row and other rows have different non-null values, in addition to speeding up the query operation, the uniqueness index can also insert or update data when the value of the field remains unique. When the values of two rows in the index are null, they are considered different, Because null and its own are not equal

• PRIMARY :

  The primary key is a special unique index, in general there is only one primary key in the Karma table, the primary key does not allow the field value to be null (and the difference between the uniqueness index), and is the smallest identifying field that can uniquely determine a row. MySQL's InnoDB stores data in the order of primary keys on disk.

• Fulltext :

  The full-text index differs from all indexes above, and the behavior of performing the retrieval in different database systems varies greatly. The sole purpose of a full-text index is to implement full-text indexing, which can only be used in the match ()/against () statement. Unlike other indexes that are implemented using B-trees or hash tables, they are implemented differently.

the same points for various indexes:

1. all the indexes are resumes in one or more fields on a
2. In addition to full-text indexing, the order of the fields is important when querying. The query must match from the first field in the index, matching the second, third ...

For example, the index is built on the COLUMN1,COLUMN2,COLUMN3,

where column1=xxx will use the index, but where COLUMN2=XXX does not use the index, unless it is a where column1=xxx and column2=xxx, which can be used as a retrieval after it is used as a retrieval, in which case the index is available. This is how the composite index is used.

Resources:

Http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL index type