MySQL Index

A MySQL index is a data structure used to speed up the speed and performance of database queries.

Establishing MySQL indexes is important for the efficient operation of MySQL; indexes can greatly improve MySQL's retrieval speed.

A MySQL index is similar to a book index. By storing pointers to data rows, it can quickly locate and access specific data in a table.

As an analogy, if a MySQL database with well-designed and properly used indexes is a Lamborghini, then a MySQL database without designed and used indexes is a human-powered tricycle.

Take the index page of a Chinese dictionary as an example: we can quickly find the needed characters by using the table of contents (index) sorted by pinyin, stroke count, radicals, and so on.

Indexes are divided into single-column indexes and composite indexes:

A single-column index means an index contains only a single column; a table can have multiple single-column indexes.
A composite index means an index contains multiple columns.

When creating an index, you need to ensure that the index is applied to the conditions in SQL query statements (generally as conditions in the WHERE clause).

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

Although indexes can improve query performance, you also need to note the following points:

Indexes require extra storage space.
When performing insert, update, and delete operations on a table, indexes need to be maintained, which may affect performance.
Too many or unreasonable indexes may cause performance degradation, so indexes need to be carefully selected and planned.

Normal Index

Indexes can significantly improve query speed, especially when searching in large tables. By using indexes, MySQL can directly locate the data rows that meet the query conditions without scanning the entire table row by row.

Create Index

UseCREATE INDEXstatement to create a normal index.

A normal index is the most common type of index, used to speed up queries on data in a table.

CREATE INDEXThe syntax is as follows:

CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

CREATE INDEX: The keyword used to create a normal index.
index_name: Specifies the name of the index to be created. The index name must be unique within the table.
table_name: Specifies on which table to create the index.
(column1, column2, ...): Specifies the table column names to be indexed. You can specify one or more columns as a composite index. The data types of these columns are usually numeric, text, or date.
ASCandDESC(Optional): Used to specify the sort order of the index. By default, the index is sorted in ascending order (ASC).

The following example assumes we have a table named students, containing id, name, and age columns. We will create a normal index on the name column.

CREATE INDEX idx_name ON students (name);

The above statement will create a normal index named idx_name on the name column of the students table, which will help improve query performance for searching by name.

Note that if the amount of data in the table is large, creating an index may take some time, but once created, query performance will be significantly improved.

Modify Table Structure (Add Index)

We can useALTER TABLEcommand to create an index in an existing table.

ALTER TABLE allows you to modify the structure of a table, including adding, modifying, or dropping indexes.

The syntax for creating an index with ALTER TABLE:

ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

ALTER TABLE: The keyword used to modify the table structure.
table_name: Specifies the name of the table to be modified.
ADD INDEX: The clause for adding an index.ADD INDEXUsed to create a normal index.
index_name: Specifies the name of the index to be created. The index name must be unique within the table.
(column1, column2, ...): Specifies the table column names to be indexed. You can specify one or more columns as a composite index. The data types of these columns are usually numeric, text, or date.
ASCandDESC(Optional): Used to specify the sort order of the index. By default, the index is sorted in ascending order (ASC).

Below is an example. We will create a normal index on an existing table named employees:

ALTER TABLE employees
ADD INDEX idx_age (age);

The above statement will create a normal index named idx_age on the age column of the employees table.

Specify Directly When Creating Table

When creating a table, you canCREATE TABLEdirectly specify indexes in the statement to create a combination of table and indexes.

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

CREATE TABLE: The keyword used to create a new table.
table_name: Specifies the name of the table to be created.
(column1 data_type, column2 data_type, ...): Defines the column names and data types of the table. You can specify one or more columns as a composite index. The data types of these columns are usually numeric, text, or date.
INDEX: The keyword used to create a normal index.
index_name: Specifies the name of the index to be created. The index name must be unique within the table.
(column1, column2, ...): Specifies the table column names to be indexed. You can specify one or more columns as a composite index. The data types of these columns are usually numeric, text, or date.
ASCandDESC(Optional): Used to specify the sort order of the index. By default, the index is sorted in ascending order (ASC).

Below is an example. We will create a table named students and create a normal index on the age column.

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  INDEX idx_age (age)
);

In the above example, we created a normal index named idx_age on the age column of the students table.

Syntax for Dropping Index

We can useDROP INDEXstatement to drop an index.

The syntax for DROP INDEX:

DROP INDEX index_name ON table_name;

DROP INDEX: The keyword used to drop an index.
index_name: Specifies the name of the index to be dropped.
ON table_name: Specifies on which table to drop the index.

UseALTER TABLEThe syntax for dropping an index with the statement is as follows:

ALTER TABLE table_name
DROP INDEX index_name;

ALTER TABLE: The keyword used to modify the table structure.
table_name: Specifies the name of the table to be modified.
DROP INDEX: The clause used to drop an index.
index_name: Specifies the name of the index to be dropped.

The following example assumes we have a table named employees with an index named idx_age on the age column. Now we want to drop this index:

DROP INDEX idx_age ON employees;

Or useALTER TABLEstatement:

ALTER TABLE employees
DROP INDEX idx_age;

Both commands will drop the index named idx_age from the employees table.

If the index does not exist, an error will occur when executing the command. Therefore, before dropping an index, it is best to confirm whether the index exists, or use an error handling mechanism to handle possible error situations.

Unique Index

In MySQL, you can useCREATE UNIQUE INDEXstatement to create a unique index.

A unique index ensures that the values in the index are unique and duplicate values are not allowed.

Create Index

CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

CREATE UNIQUE INDEX: The combination of keywords used to create a unique index.
index_name: Specifies the name of the unique index to be created. The index name must be unique within the table.
table_name: Specifies on which table to create the unique index.
(column1, column2, ...): Specifies the table column names to be indexed. You can specify one or more columns as a composite index. The data types of these columns are usually numeric, text, or date.
ASCandDESC(Optional): Used to specify the sort order of the index. By default, the index is sorted in ascending order (ASC).

The following is an example of creating a unique index: Suppose we have a table named employees with id and email columns. Now we want to create a unique index on the email column to ensure that each employee's email address is unique.

CREATE UNIQUE INDEX idx_email ON employees (email);

The above example will create a unique index named idx_email on the email column of the employees table.

Modify Table Structure to Add Index

We can useALTER TABLEcommand to create a unique index.

ALTER TABLEThe command allows you to modify the structure of an existing table, including adding new indexes.

ALTER table table_name 
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);

ALTER TABLE: The keyword used to modify the table structure.
table_name: Specifies the name of the table to be modified.
ADD CONSTRAINT: This is the keyword used to add constraints (including unique indexes).
unique_constraint_name : Specifies the name of the unique index to be created. The constraint name must be unique within the table.
UNIQUE (column1, column2, ...): Specifies the table column names to be indexed. You can specify one or more columns as a composite index. The data types of these columns are usually numeric, text, or date.

The following is a use ofALTER TABLEExample of creating a unique index with a command: Suppose we have a table named employees that contains id and email columns, and now we want to create a unique index on the email column to ensure that each employee's email address is unique.

ALTER TABLE employees
ADD CONSTRAINT idx_email UNIQUE (email);

The above example will create a unique index named idx_email on the email column of the employees table.

Note that if there are already duplicate email values in the table, adding a unique index will fail. Before creating a unique index, you may need to make sure that the email column in the table has no duplicate values.

Specify Directly When Creating Table

We can also create the table and, at the same time, you can use theCREATE TABLEkeyword in theUNIQUEstatement to create a unique index.

This defines the unique index constraint at the same time as the table is created.

CREATE TABLESyntax for creating a unique index in the statement:

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

CREATE TABLE: Keyword used to create a new table.
table_name: Specifies the name of the table to be created.
(column1 data_type, column2 data_type, ...): Defines the column names and data types of the table. You can specify one or more columns as the index combination. The data types of these columns are usually numeric, text, or date.
CONSTRAINT: Keyword used to add a constraint.
index_name: Specifies the name of the unique index to be created. The constraint name must be unique within the table.
UNIQUE (column1, column2, ...): Specifies the table column name to be indexed.
ASCandDESC(Optional): Used to specify the sort order of the index. By default, the index is sorted in ascending order (ASC).

The following is an example of creating a unique index when creating a table: Suppose we want to create a table named employees that contains id, name, and email columns, and we want the values in the email column to be unique, so we define a unique index when creating the table.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100) UNIQUE
);

In this example, the email column is defined as a unique index because the UNIQUE keyword is added after it.

Note that after using theUNIQUEkeyword, the index name is automatically generated, and you can also specify an index name as needed.

Use ALTER Command to Add and Drop Indexes

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. The values in the primary key column must be unique. The list of primary key columns can be one or more columns, and cannot contain NULL values.
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):The values 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):Adds a normal index; index values 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 adds 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 drop an index. Try the following example to drop an index:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Use ALTER Command to Add and Drop Primary Keys

A primary key acts on a column (it can be a single column or multiple columns combined as a primary key). When adding a primary key, you need to ensure that the primary key is not null by default (NOT NULL). 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 drop a primary key:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

Display Index Information

You can use theSHOW INDEXcommand to list the relevant index information in a table.

You can add\Gto format the output information.

SHOW INDEXStatement:

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

SHOW INDEX: Keyword used to display index information.
FROM table_name: Specifies the name of the table whose index information you want to view.
\G: Format the output information.

After executing the above command, detailed information about all indexes in the specified table will be displayed, including the index name (Key_name), index column (Column_name), whether it is a unique index (Non_unique), sort method (Collation), index cardinality (Cardinality), and so on.

Other extensions

MySQL Programming Language

MySQL Index

Normal Index

Create Index

Modify Table Structure (Add Index)

Specify Directly When Creating Table

Syntax for Dropping Index

Unique Index

Create Index

Modify Table Structure to Add Index

Specify Directly When Creating Table

Use ALTER Command to Add and Drop Indexes

Use ALTER Command to Add and Drop Primary Keys

Display Index Information