Provides you with an in-depth understanding of MySQL index types

Source: Internet
Author: User
Tags mysql index

The MySQL index type is the basic knowledge in MySQL. The following describes the MySQL index type in detail. If you can learn more about the MySQL index type.

1. Common Index

A common INDEX is an INDEX defined by a keyword KEY or INDEX. The only task is to speed up data access. Therefore, you should create an index only for the data columns that most frequently appear in the query condition WHEREcolumn =) or the sorting condition ORDERBYcolumn. If possible, you should select the most neat and compact data column, such as an integer data column) to create an index.

2. Unique Index

Normal indexes allow indexed data columns to contain duplicate values. For example, because a person may have the same name, the same name may appear twice or more times in the same "employee profile" data table.

If you can determine that a data column will only contain different values, you should use the keyword UNIQUE to define it as a UNIQUE index when creating an index for this data column. The advantage of doing so: First, it simplifies MySQL's management of this index, and this index becomes more efficient. Second, MySQL inserts a data table with a new record, automatically checks whether the value of this field of the new record has already exists in this field of a record; if yes, MySQL rejects the insert of that new record. That is to say, the unique index can ensure the uniqueness of data records. In fact, in many cases, the purpose of creating a unique index is not to speed up access, but to avoid data duplication.

3. Primary Index

I have already repeatedly stressed that an index must be created for the primary key field. This index is called a "primary index ". The only difference between a PRIMARY index and a UNIQUE index is that the keywords used by the former during definition are PRIMARY rather than UNIQUE.

4. Foreign Key Index

If a foreign key constraint is defined for a foreign key field, MySQL defines an internal index to help you manage and use foreign key constraints in the most efficient way.

5. Composite Index

Indexes can cover multiple data columns, such as INDEXcolumnA and columnB. This index features that MySQL can selectively use such an index. If you only need to use an index on the columnA data column for the query operation, you can use the composite index INDEXcolumnA, columnB ). However, this method is only applicable to the combination of data columns in the composite index. For example, INDEXA, B, C) can be used as an index of A or A, B), but cannot be used as an index of B, C, or B, C.

6. Index Length

When defining indexes for CHAR and VARCHAR data columns, you can limit the index length to a given number of characters, which must be less than the maximum number of characters allowed by this field ). The advantage of this is that you can generate an index file with a relatively small size and fast retrieval speed. In most applications, most of the string data in the database is based on a variety of names, and the index length is set to 10 ~ 15 characters is enough to narrow the search range to a few data records. When creating indexes for BLOB and TEXT data columns, you must limit the index length; mySQL allows a normal index on the text field of the largest index in the full text index to accelerate the retrieval of the string that is at the beginning of the field content, that is, the character at the beginning of the field content. If a field contains a large text segment consisting of several or even multiple words, the normal index will be useless. This kind of search usually appears in the form of, which is very complicated for MySQL. If the data volume to be processed is large, the response time will be very long.

This type of scenario is where full-text index full-textindex can be used. When an index of this type is generated, MySQL creates a list of all the words that appear in the text, and searches for relevant data records based on the list. The full-text index can be created along with the data table, or you can use the following command to add a full-text index if necessary in the future:

ALTERTABLEtablenameADDFULLTEXTcolumn1, column2) with full-text indexes, you can use the SELECT query command to retrieve data records containing one or more given words. The basic syntax for such query commands is as follows:

SELECT * FROMtablename

WHEREMATCHcolumn1, column2) AGAINST 'word1 ', 'word2', 'word3 ')

The preceding command will query all the data records of word1, word2, and word3 in column1 and column2 fields.

Note: Full-text indexing is not supported for InnoDB data tables.

MySQL foreign key and reference integrity

Seven considerations for converting MySQL to Oracle

Detailed description of the actual configuration of SlowLog in MySQL

Take you to understand the features of MySQL

View MySQL database table commands

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: 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.