Mysql index type

Mysql index type

MYSQL indexes are classified into the following types:

• KEY or INDEX:

  A normal index is a general index different from a unique index. The column where the index is located can contain the same value. Therefore, the rows in the index may contain identical rows. These indexes do not impose any restrictions on data, just to speed up data query operations .)

• UNIQUE:

  The unique index value is that all the rows in the index (created on certain columns) must be unique. That is to say, the values of fields in a certain index row have different non-NULL values than those in other rows, in addition to speeding up the query operation, the unique index can also be used to keep the value of this field unique when data is inserted or updated. When the values of both rows in the index are NULL, they are considered different because NULL is not equal to itself. However, the NULL value of the index field is generally avoided)

• PRIMARY:

  A primary key is a special unique index. Generally, there is only one primary key in the parent table. The primary key does not allow the field value to be NULL (different from the unique index ), is the smallest Identifier Field that uniquely identifies a row. Mysql InnoDB stores data on disks in the order of primary keys.

• FULLTEXT:

  Full-text indexes are different from all of the above indexes, and perform searches in different database systems. The only purpose of full-text index is to achieve full-text search. It can only be used in MATCH ()/AGAINST () statements. Different from other indexes implemented using the B-tree or hash table, the implementation method is different.

Similarities of various indexes:

1. All indexes are indexed by the resume on one or more fields.
2. In addition to full-text retrieval, the order of fields during query is very important. When querying, you must match the first field of the index, matching the second and third fields in the index in sequence ......

For example, indexes are created on column1, column2, and column3,

Where column1 = xxx will use an index, but where column2 = xxx will not use an index, unless it is where column1 = xxx and column2 = xxx and when the field before it is used for retrieval, it can be used for retrieval. In this case, indexes can be used. This is how to use composite indexes.

References:

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

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.