MySQL index introduction and differences between common indexes (primary key index, unique index, normal index, full-text index, composite index)

Source: Internet
Author: User
Tags mysql index

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 + tree in the search for a single record speed, although not compared to the hash index, But because it is more suitable for sorting and other operations, so 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 ')
Combined 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.

Reprint Please specify address: http://www.phpddt.com/db/mysql-index.html respect for the work of others is to respect their own!

Description of MySQL index and differences between common indexes (primary key index, unique index, normal index, full-text index, composite 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.