Creation and Efficiency Comparison of unique indexes of mysql common indexes

Unique (unique index)

Alter table 'table _ name' add unique ('column ')

Index (Common index)
Mysql tutorial> alter table 'table _ name' add index index_name ('column ')

Fulltext (full-text index)
Alter table 'table _ name' add fulltext ('column ')

 

1. Common Index

The only task of a common index (index defined by the key or index keyword) 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 sort condition (orderbycolumn. If possible, you should select the most neat and compact data column (such as an integer data column) to create an index.

An article library contains two tables: category and article. There are 10 classified data entries in category. There are 0.2 million articles in articles. There is a "article_category" field in article that corresponds to the "category_id" field in category. In the article table, the article_category is defined as an index. The database tutorial size is 1.3 GB.

Problem description:

Execute a common query: select * from 'Article' where article_category = 11 order by article_id desc limit 5. The execution time is about 5 seconds.

Solution:

Create index idx_u on article (article_category, article_id );

Select * from 'Article' where article_category = 11 order by article_id desc limit 5 reduced to 0.0027 seconds

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.

Create table persons
(
Id_p int not null,
Lastname varchar (255) not null,
Firstname varchar (255 ),
Address varchar (255 ),
City varchar (255 ),
Constraint uc_personid unique (id_p, lastname)
)

To revoke the unique constraint, use the following SQL statement:

Mysql:
Alter table persons
Drop index uc_personid

 

Unique index.
It is similar to the previous "normal index". The difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:
(1) create an index: create unique index indexname on tablename (tablecolumns (length ))
(2) modify the table structure: alter tablename add unique [indexname] on (tablecolumns (length ))
(3) when creating a table, specify: create table tablename ([...], unique [indexname] (tablecolumns (length ));