Explanation of MySQL Index Types Normal Unique and Full Text

The index types of MySQL include general index (normal), only one index (unique) and full-text index (full text). Reasonable use of indexes can greatly improve the query efficiency of databases. The following are the introduction of three types of indexes

normal: This is the most basic index, and it has no restrictions. The default index of BTREE type in MyIASM is the index we use in most cases.

unique: Represents a 1-only index that does not allow duplicates if the field information is guaranteed not to be duplicated. For example, when the ID number is used as an index, it can be set to unique.

full text: Represents an index for full-text searches and is available only for MyISAM tables. FULLTEXT works best when searching for a long article. For relatively short text, remember that for large-capacity data tables, generating full-text indexes is a very time-consuming and hard disk space-consuming practice.

mysql Index Type Normal, Unique, Full Text Difference

Normal:

Represents a common index, which can be used in most cases

Unique:

Constraint Uniqueness identifies every record in a database table, i.e. in a single table, it is not possible to use each record as Uniqueness (for example, ID card is Uniqueness), Unique (column Uniqueness is required), and Primary Key (primary key = unique + not null column Uniqueness) constraints all provide uniqueness guarantee in columns or column sets. Primary Key has automatically defined Unique constraints, but can have multiple Unique constraints in each table, but only one ES50Key constraints.

Creating Unique Constraints in mysql

Full Text:

Full-text retrieval, in the retrieval of long text, the best effect, short text recommended to use Index, but in the retrieval of data is relatively large, now put the data into a table without a global index, and then in the Full Text index created by Create Index, than first for a table to establish Full Text and then write data a lot faster

To sum up, the categories of indexes are determined by the content characteristics of the fields that are indexed, and normal is the most common in general.

In the actual operation, which fields in the table should be selected as indexes?

In order to make the use of indexes more efficient, when creating indexes, we must consider which fields to create indexes and what types of indexes to create. There are seven principles:

1. Choose a uniqueness index

The uniqueness index has a value of uniqueness, which makes it easier to determine a record through the index. For example, the number in the student table is a uniqueness field. Establishing a unique index for this field can quickly determine the information of a student. If you use a name, you may have the same name, which reduces the query speed.

2. Indexing fields that often require sorting, grouping, and federation

Fields for operations such as ORDER BY, GROUP BY, DISTINCT, and UNION are often required, and sorting operations waste a lot of time. If you index it, you can effectively avoid sorting operations.

3. Indexing fields that are often used as query criteria

If a field is frequently used as a query condition, the query speed of that field will affect the query speed of the whole table. Therefore, indexing such fields can improve the query speed of the whole table.

4. Limit the number of indexes

The number of indexes is not as good as possible. Each index needs to occupy disk space, and the more indexes, the larger the disk space required. When modifying a table, it is troublesome to reconstruct and update the index. The more indexes, the more time it takes to update the table.

5. Try to use indexes with less data

If the value of the index is very long, the speed of the query will be affected. For example, a full-text retrieval of a field of type CHAR (100) must take more time than a field of type CHAR (10).

6. Try to use prefixes for indexing

If the value of the index field is very long, it is best to index it with the prefix of the value. For example, for fields of type TEXT and BLOG, full-text retrieval is a waste of time. If only the first few characters of the field are retrieved, the retrieval speed can be improved.

7. Delete indexes that are no longer used or rarely used

After the data in the table is greatly updated, or the way the data is used is changed, the original indexes may no longer be needed. Database administrators should periodically locate these indexes and delete them to reduce the impact of indexes on update operations.

Note: The ultimate goal of selecting an index is to make the query faster. The principles given above are the most basic principles, but we can't stick to the above principles. Readers should continue to practice in their future study and work. According to the actual situation of application, analyze and judge, and choose the most suitable index mode.

For example, for example, you are making a membership card system for a shopping mall. This system has one membership table (the approximate fields are as follows):

Member number INT
Member name VARCHAR (10)
Member ID number VARCHAR (18)
Member telephone VARCHAR (10)
Member address VARCHAR (50)
Member Remarks Information TEXT

Then this member number, as the primary key, uses PRIMARY

If the member name is to be indexed, it is an ordinary INDEX

If you want to index the member ID number, you can choose UNIQUE (only 1, no duplication is allowed)

If you need to index the member remarks information, you can choose FULLTEXT and search in full text.

Summarize