Differences between MySQL primary key index, unique index, normal index, full-text index, combined index

Source: Internet
Author: User
Tags mysql index

Original: MySQL primary key index, unique index, normal index, full-text index, composite index difference

MySQL Index concept:
Say the MySQL index, see a few example: index is like a book directory, it will let you find content faster, obviously the directory (index) is not the more the better, if the book 1000 pages, 500 is also the directory, it is of course inefficient, the directory is to occupy the paper, and the index is to occupy disk space.


The MySQL index has two main structures:B + Tree and hash.

Hash: Hsah Index is less useful in MySQL, he organizes the index of the data in hash form, so it is very fast when looking for a record. At that time, because it is a hash structure, each key only corresponds to one value, And is the way the hash is distributed. So he doesn't support features such as range lookup and sorting .

B + Tree:B+tree is the most frequently used index data structure in MySQL, the data structure is organized in the form of a balanced tree, because it is a tree structure, so it is more suitable for processing sorting, scope lookup and other functions. Relative hash index, B + The tree finds a single record faster than a hash index, but because it is more suitable for sorting operations, he is more popular with users. After all, it is not possible to do a single record of the database.


MySQL common indexes are: primary key index, unique index, normal index, full-text index, composite index

primary Key (primary key index)   alter TABLE ' table_name ' ADD PRIMARY KEY (' column ')  unique (unique index)      ALTER TABLE ' table_name ' ADD UNIQUE (' column ')
index (normal index)      ALTER TABLE ' table_name ' ADD INDEX index_name (' column ')  fulltext ( Full-text index)       alter TABLE ' table_name ' ADD fulltext (' column ')
combination index    ALTER TABLE ' table_name ' ADD index index_name (' Column1 ', ' column2 ', ' column3 ')  

MySQL various index differences:
Normal index: The most basic index, without any restrictions
Unique index: Similar to "normal index", the difference is that the value of the indexed column must be unique, but a null value is allowed.
Primary KEY index: It is a special unique index and is not allowed to have null values.
Full-Text indexing: Available only for MyISAM tables, generating full-text indexes is a time-consuming space for larger data.
Combined index: For more MySQL efficiency, you can create a composite index that follows the "leftmost prefix" principle.

Differences between MySQL primary key index, unique index, normal index, full-text index, combined index

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.