MySQL Indexes

The establishment of MySQL indexes is very important for the efficient operation of MySQL, as indexes can greatly improve the retrieval speed of MySQL.

To put it another way, if a well-designed and indexed MySQL is a Lamborghini, then a MySQL without design and indexing is like a human-powered tricycle.

Using the directory page (index) of a Chinese dictionary as an example, we can quickly find the needed character by sorting the directory (index) by pinyin, strokes, or radicals.

Indexes are divided into single-column indexes and composite indexes. A single-column index means that an index only contains a single column, and a table can have multiple single-column indexes, but this is not a composite index. A composite index means that an index contains multiple columns.

When creating an index, you need to ensure that the index is applied to the conditions of the SQL query statement (usually as the condition of the WHERE clause).

In fact, an index is also a table, which stores the primary key and index fields and points to the records of the entity table.

Above all, we have been talking about the benefits of using indexes, but excessive use of indexes will cause abuse. Therefore, indexes also have their disadvantages: although indexes greatly improve query speed, they will also slow down the speed of updating the table, such as INSERT, UPDATE, and DELETE operations on the table. Because when updating the table, MySQL not only needs to save the data but also save the index file.

Creating an index will occupy disk space for the index file.

Normal index

Create index

This is the most basic index, which has no restrictions. It can be created in the following ways:

CREATE INDEX indexName ON table_name (column_name)

For CHAR and VARCHAR types, the length can be less than the actual field length; for BLOB and TEXT types, the length must be specified.

Modify table structure (add index)

ALTER table tableName ADD INDEX indexName(columnName)

Specify directly when creating a table

CREATE TABLE mytable(　　
　
ID INT NOT NULL,　　　
　
username VARCHAR(16) NOT NULL,　　
　
INDEX [indexName] (username(length))　　
　
);

Syntax for deleting an index

DROP INDEX [indexName] ON mytable;

Unique index

It is similar to the previous normal index, the difference being: the values of the indexed columns must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It has the following creation methods:

Create index

CREATE UNIQUE INDEX indexName ON mytable(username(length))

Modify table structure

ALTER table mytable ADD UNIQUE [indexName] (username(length))

Specify directly when creating a table

CREATE TABLE mytable(　　
　
ID INT NOT NULL,　　　
　
username VARCHAR(16) NOT NULL,　　
　
UNIQUE [indexName] (username(length))　　
　
);

Add and delete indexes using the ALTER command

There are four ways to add indexes to a data table:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): This statement adds a primary key, which means the index value must be unique and cannot be NULL.

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): The value of the index created by this statement must be unique (except for NULL, which may appear multiple times).

• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Add a normal index, where the index value can appear multiple times.

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):This statement specifies the index as FULLTEXT, used for full-text indexing.

The following example shows how to add an index to a table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can also use the DROP clause in the ALTER command to delete indexes. Try the following example to delete an index:

mysql>　ALTER　TABLE　testalter_tbl　DROP　INDEX　c;

Using ALTER command to add and delete primary key

The primary key acts on the column (can be a single column or a combination of multiple columns as a composite primary key). When adding a primary key index, you need to ensure that the primary key is not null by default (NOT NULL). Here is an example:

mysql>　ALTER　TABLE　testalter_tbl　MODIFY　i　INT　NOT　NULL;
mysql>　ALTER　TABLE　testalter_tbl　ADD　PRIMARY　KEY　(i);

You can also use the ALTER command to delete the primary key:

mysql>　ALTER　TABLE　testalter_tbl　DROP　PRIMARY　KEY;

When deleting the primary key, you only need to specify PRIMARY KEY, but when deleting an index, you must know the index name.

Display Index Information

You can use the SHOW INDEX command to list the related index information in the table. You can add \G to format the output information.

Try the following examples:

mysql>　SHOW　INDEX　FROM　table_name;　\G
........