What is the difference between normal, unique, and full text indexes of mysql?

What is the difference between normal, unique, and full text indexes of mysql?

What are the differences between normal, unique, and full text indexes of mysql?

PRIMARY key. Is unique and cannot be empty.

INDEX, which is common.

Normal: indicates a normal index.

Unique: unique. Duplicate indexes are not allowed. If the field information is not repeated. For example, when the ID card number is used as an index, it can be set to unique.

Full textl: full-text search index. When FULLTEXT is used to search for a long article, it works best. It is used in short text. If there are only one or two lines of text, the normal INDEX is also acceptable.

In summary, the index category is determined by the content characteristics of the index field. Normally, normal is the most common.

During actual operations, which fields in the table should be selected as indexes?

To make indexes more efficient, you must consider which fields to create indexes and what types of indexes to create when creating indexes. There are seven principles:

1. Select a unique index

The value of a unique index is unique and can be used to determine a record more quickly. For example, the student ID in the student table is a unique field. Creating a unique index for this field can quickly determine the information of a student. If the name is used, the same name may exist, thus reducing the query speed.

2. Create an index for fields that often require sorting, grouping, and Union operations

Fields that require operations such as order by, group by, DISTINCT, and UNION are often time-consuming. If you create an index for it, you can effectively avoid sorting operations.

3. Create an index for fields that are often used as query Conditions

If a field is often used as a query condition, the query speed of this field affects the query speed of the entire table. Therefore, creating indexes for such fields can improve the query speed of the entire table.

4. Limit the number of Indexes

The more indexes, the better. Each index occupies disk space. The more indexes, the larger the disk space required. It is difficult to reconstruct and update indexes when modifying tables. The more indexes, the more time it takes to update the table.

5. Try to use indexes with a small amount of data

If the index value is long, the query speed will be affected. For example, it takes more time to search the full text of a CHAR (100) field than that of CHAR (10.

6. Try to use the prefix for indexing.

If the index field value is long, it is best to use the value prefix to index. For example, full-TEXT search for TEXT and BLOG fields is a waste of time. If you retrieve only the first several characters of a field, the search speed can be improved.

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

After the data in the table is updated in large quantities or the usage of the data is changed, some of the original indexes may no longer be needed. Database Administrators should regularly identify and delete these indexes to reduce the impact of indexes on update operations.

Note: The final purpose of selecting an index is to make the query faster. The principle given above is the most basic principle, but cannot stick to the above principle. Readers should continue to practice in their future studies and work. Analyze and judge based on the actual situation of the application, and select the most appropriate index method.

For example, you are creating a membership card system for a mall. This system has a member table (the approximate field is as follows ):

Member ID INT
Member name VARCHAR (10)
Member ID number VARCHAR (18)
Member phone number VARCHAR (10)
Member Address VARCHAR (50)
Member remarks TEXT

The member number is used as the PRIMARY key and PRIMARY is used.

If you want to create an INDEX for the member name, it is a normal INDEX.

If you want to create an index for the member ID card number, you can select UNIQUE (UNIQUE, not allowed to be repeated)

If you need to create an index for member remarks, you can select FULLTEXT for full-text search.

I will repost several more advanced and thorough analysis articles on indexes:

Database indexing

Data Structure and algorithm principles behind MySQL Indexes

Understanding MySQL-index and Optimization