Explanation of MySQL index types Normal, Unique and Full Text

MySQL's index types include normal index, unique index and full text index. Proper use of indexes can greatly improve the query efficiency of the database. The following is an introduction to the three types of indexes.

Normal: This is the most basic index. It has no restrictions. The default BTREE type index in MyIASM is the index we use in most cases.

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

full text: Indicates an index for full-text search, which is only available for MyISAM tables. FULLTEXT works best when searching for very long text. Used for shorter texts. Remember that for large-capacity data tables, generating full-text indexes is a very time-consuming and disk-space-consuming practice.

The difference between mysql index types Normal, Unique, and Full Text

Normal:

Indicates a normal index, which can be used in most cases

Unique:

Constraints uniquely identify each record in a database table, that is, each record in a single table cannot be unique (for example, an ID card is unique). Unique (requires column uniqueness) and Primary Key (primary key = unique + not null column uniqueness) constraints both provide uniqueness guarantees for columns or column sets. Primary Key has an automatically defined Unique constraint, but each table can have multiple Unique constraints, but can only have one Primary Key constraint.

Creating a Unique constraint in MySQL

Full Text:

Indicates full-text search. It works best when searching long texts. It is recommended to use Index for short texts. However, when searching for large amounts of data, it is much faster to put the data into a table without a global index and then use Create Index to create a Full Text index than to create a Full Text index for a table first and then write data.

In summary, the type of index is determined by the content characteristics of the indexed field, and normal is usually the most common.

In 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, you must consider which fields to create indexes on and what type of index to create. There are seven major principles:

1. Select unique index

The value of a unique index is unique, and you can use this index to identify 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 a student's information. If you use the full name, there may be duplicate names, which will slow down the search.

2. Create indexes for fields that often require sorting, grouping, and union operations

Fields that often require operations such as ORDER BY, GROUP BY, DISTINCT, and UNION will waste a lot of time in sorting operations. If you create an index for it, you can effectively avoid sorting operations.

3. Create indexes for fields that are often used as query conditions

If a field is frequently used as a query condition, the query speed of this field will affect 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 requires disk space. The more indexes you have, the more disk space you need. When modifying a table, rebuilding and updating the index is troublesome. The more indexes there are, the more time consuming it becomes to update the table.

5. Try to use indexes with less data

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

6. Try to use prefix indexing

If the value of the indexed field is very long, it is better to use a prefix of the value to index. For example, for TEXT and BLOG type fields, full-text search will be a waste of time. If you only search the first few characters of a field, this can increase the search speed.

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

When the data in the table is updated in large quantities or the way the data is used is changed, some of the original indexes may no longer be needed. Database administrators should regularly identify these indexes and delete them to reduce the impact of the 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 guidelines, but you cannot stick to them. 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 indexing method.

For example, let’s say you are developing a membership card system for a shopping mall. This system has a member table (the general fields are as follows):

Member Number INT
Member name VARCHAR(10)
Member ID number VARCHAR(18)
Member phone number VARCHAR(10)
Member's address VARCHAR(50)
Member Remarks TEXT

Then this membership number is used as the primary key using PRIMARY

If you want to create an index for member names, then it is a normal INDEX

If you want to create an index for the member ID number, you can choose UNIQUE (unique, no duplication allowed)

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links