Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of MySQL index types Normal, Unique and Full Text. I hope you will get something after reading this article. Let's discuss it together.

The index types of MySQL are general index (normal), unique index (unique) and full-text index (full text). Rational use of index can greatly improve the query efficiency of the database. Here is an introduction to the three types of index.

Normal: this is the most basic index, it has no restrictions, and the default index of type BTREE 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 duplicated. For example, when the ID number is used as an index, it can be set to unique.

Full text: an index that represents a full-text search, available only for MyISAM tables. FULLTEXT works best when searching for a long article. For short text, keep in mind that for large data tables, generating a full-text index is a very time-consuming and hard disk space-consuming practice.

Mysql index type Normal,Unique,Full Text distinction

Normal:

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

Unique:

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

Create Unique constraints in mysql

Full Text:

For full-text retrieval, it works best when retrieving long text. Index is recommended for short text, but when the amount of data is large, the data is now put into a table without a global index, and then the Full Text index created with Create Index is much faster than building Full Text for a table and then writing data.

To sum up, the category of the index is determined by the content characteristics of the indexed field, and normal is usually the most common.

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

In order to make the use of the index more efficient, when creating an index, you must consider which fields to create the index and what type of index to create. There are seven general principles:

1. Select the uniqueness index

The value of the uniqueness index is unique, and a record can be determined more quickly through the index. For example, the student number in the student table is a unique field. Establishing a unique index for this field can quickly determine the information of a student. If a name is used, there may be a phenomenon of the same name, which slows down the query speed.

2. Index fields that often require sorting, grouping, and federation operations

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

3. Index fields that are often used as query criteria

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

4. Limit the number of indexes

It is not as many indexes as possible. Each index takes up disk space, and the more indexes you have, the more disk space you need. When modifying a table, it is troublesome to reconstruct and update the index. The more indexes you have, the more time it takes to update the table.

5. Use indexes with less data as much as possible

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

6. Try to use prefixes to index

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

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

After the data in the table is updated massively, or the way the data is used is changed, some of the original indexes may no longer be needed. Database administrators should periodically find these indexes and delete them, thereby reducing 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 should not stick to them. Readers should continue to practice in their future study and work. Analyze and judge according to the actual situation of the application, and choose the most appropriate index method.

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

Member number INT

Member name VARCHAR (10)

Member identity card number VARCHAR (18)

Member telephone number VARCHAR (10)

Member address VARCHAR (50)

Member remarks information TEXT

So this membership 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 card number, you can choose UNIQUE (unique, no repetition is allowed)

If you need to index member remarks, you can choose FULLTEXT, full-text search.

After reading this article, I believe you have some understanding of "sample Analysis of MySQL Index types Normal, Unique and Full Text". If you want to know more about it, please follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.