MySQL Index Types and Related Operations

I. Introduction

Currently, MySQL mainly has the following index types:

1. Normal Index
2. Unique Index
3. Primary Key Index
4. Composite Index
5. Full-Text Index

II. Statements

CREATE TABLE table_name[col_name data type]​
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1. unique|fulltext are optional parameters, representing unique index and full-text index respectively.
2. index and key are synonyms, which have the same function and are used to specify the creation of an index.
3. col_name is the field column for which the index needs to be created, and this column must be selected from multiple columns defined in the data table.
4. index_name specifies the name of the index, which is an optional parameter. If not specified, the default col_name is the index value.
5. length is an optional parameter, indicating the length of the index. Only fields of string type can specify the index length.
6. asc or desc specifies the storage of index values in ascending or descending order.

III. Index Types

1. Normal Index

It is the most basic index with no restrictions. It can be created in the following ways:

• Creating an index directly

CREATE INDEX index_name ON table(column(length))

• Adding an index by modifying the table structure

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

• Creating an index while creating the table

CREATE TABLE `table` (​
    `id` int(11) NOT NULL AUTO_INCREMENT ,​
    `title` char(255) CHARACTER NOT NULL ,​
    `content` text CHARACTER NULL ,​
    `time` int(10) NULL DEFAULT NULL ,​
    PRIMARY KEY (`id`),​
    INDEX index_name (title(length))​
)

• Deleting an index

DROP INDEX index_name ON table

2. Unique Index

Similar to the normal index mentioned above, the difference is that the values of the index column must be unique, but null values are allowed. For a composite index, the combination of column values must be unique. It can be created in the following ways:

• Creating a unique index

CREATE UNIQUE INDEX indexName ON table(column(length))

• Modifying the table structure

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

• Specifying directly when creating the table

CREATE TABLE `table` (​
    `id` int(11) NOT NULL AUTO_INCREMENT ,​
    `title` char(255) CHARACTER NOT NULL ,​
    `content` text CHARACTER NULL ,​
    `time` int(10) NULL DEFAULT NULL ,​
    UNIQUE indexName (title(length))​
);

3. Primary Key Index

It is a special type of unique index. A table can have only one primary key, and null values are not allowed. It is generally created when the table is built:

CREATE TABLE `table` (​
    `id` int(11) NOT NULL AUTO_INCREMENT ,​
    `title` char(255) NOT NULL ,​
    PRIMARY KEY (`id`)​
);

4. Composite Index

Refers to an index created on multiple fields. The index will be used only if the first field used when creating the index is used in the query condition. The leftmost prefix rule should be followed when using a composite index.

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

5. Full-Text Index

It is mainly used to find keywords in the text rather than directly comparing with the values in the index. The full-text index is quite different from other indexes; it is more like a search engine rather than a simple parameter match in the where clause. The full-text index is used with the match against operation instead of the general where clause plus like. It can be used in create table, alter table, and create index. However, currently, full-text indexes can only be created on char, varchar, and text columns. It is worth mentioning that when the amount of data is large, putting the data into a table without a global index first and then creating a full-text index with CREATE index is much faster than building a full-text index for a table first and then writing the data into it.

• Adding a full-text index when creating the table

CREATE TABLE `table` (​
    `id` int(11) NOT NULL AUTO_INCREMENT ,​
    `title` char(255) CHARACTER NOT NULL ,​
    `content` text CHARACTER NULL ,​
    `time` int(10) NULL DEFAULT NULL ,​
    PRIMARY KEY (`id`),​
    FULLTEXT (content)​
);

• Adding a full-text index by modifying the table structure

ALTER TABLE article ADD FULLTEXT index_content(content)

• Creating an index directly

CREATE FULLTEXT INDEX index_content ON article(content)

IV. Disadvantages

1. Although indexes greatly improve query speed, they will reduce the speed of updating the table, such as performing insert, update, and delete operations on the table. Because when updating the table, not only the data but also the index files need to be saved.
2. Creating indexes will occupy disk space for index files. Generally, this is not a serious problem, but if you create multiple composite indexes on a large table, the index files will grow very quickly.

Index is only one factor to improve efficiency. If there is a table with a large amount of data, you need to spend time researching to establish the best index or optimize the query statements.

V. Notes

When using indexes, there are the following tips and precautions:

1. Indexes will not include columns with null values

As long as a column contains null values, it will not be included in the index. In a composite index, if any column contains null values, that column is invalid for this composite index. Therefore, we should not set the default value of a field to null when designing the database.

2. Using short indexes

When indexing string columns, you should specify a prefix length if possible. For example, if there is a char(255) column, and most values are unique within the first 10 or 20 characters, then there is no need to index the entire column. Short indexes can not only improve query speed but also save disk space and I/O operations.

3. Sorting of index columns

A query uses only one index. Therefore, if an index is already used in the where clause, the columns in order by will not use the index. So, do not use sorting operations if the default sorting of the database can meet the requirements; try not to include sorting of multiple columns, and if necessary, it is better to create composite indexes for these columns.

4. Like statement operation

In general, it is not recommended to use the like operation. If it is necessary to use it, how to use it is also a problem. like “%aaa%” will not use the index, while like “aaa%” can use the index.

5. Do not perform operations on columns

This will cause the index to be invalid and result in a full table scan. For example:

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6. Do not use not in and <> operations