YouTip

MySQL index is a data structure used to speed up database queries and improve performance.

Creating indexes in MySQL is crucial for its efficient operation, as indexes can significantly enhance MySQL's retrieval speed.

MySQL indexes are similar to book indexes. By storing pointers to data rows, they enable quick location and access to specific data in a table.

For example, if a well-designed and indexed MySQL is a Lamborghini, then a MySQL without proper design and indexing is a human-powered tricycle.

Using the table of contents (index) of a Chinese dictionary as an analogy, we can quickly find the desired character through indexes sorted by pinyin, stroke count, radical, etc.

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

• Single-column index: An index that contains only one column. A table can have multiple single-column indexes.
• Composite index: An index that contains multiple columns.

When creating an index, you need to ensure that the index is applied to the conditions in SQL queries (typically as the WHERE clause condition).

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

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

• Indexes require additional storage space.
• When performing insert, update, and delete operations on a table, indexes need to be maintained, which may affect performance.
• Excessive or improper indexes may lead to performance degradation, so indexes should 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 data rows that meet the query conditions without scanning the entire table row by row.

Creating an Index

Use the CREATE INDEX statement to create a normal index.

Normal indexes are the most common index type, used to speed up queries on table data.

Syntax for CREATE INDEX:

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

• CREATE INDEX: 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 the table on which 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 typically numeric, text, or date.
• ASC and DESC (optional): Used to specify the sort order of the index. By default, indexes are sorted in ascending order (ASC).

The following example assumes we have a table named students with columns id, name, and age. 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 when searching by name.

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

Modifying Table Structure (Adding an Index)

We can use the ALTER TABLE command to create an index on an existing table.

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

Syntax for creating an index with ALTER TABLE:

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

• ALTER TABLE: Keyword used to modify the table structure.
• table_name: Specifies the name of the table to be modified.
• ADD INDEX: Clause for adding an index. ADD INDEX is 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 typically numeric, text, or date.
• ASC and DESC (optional): Used to specify the sort order of the index. By default, indexes are sorted in ascending order (ASC).

Here is an example where 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.

Specifying Directly When Creating the Table

We can specify the index directly in the CREATE TABLE statement when creating the table, combining table and index creation.

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    INDEX index_name (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 a composite index. The data types of these columns are typically numeric, text, or date.
• INDEX: 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 typically numeric, text, or date.
• ASC and DESC (optional): Used to specify the sort order of the index. By default, indexes are sorted in ascending order (ASC).

Here is an example where we create a table named students and define 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 Deleting an Index

We can use the DROP INDEX statement to delete an index.

Syntax for DROP INDEX:

DROP INDEX index_name ON table_name;

• DROP INDEX: Keyword used to delete an index.
• index_name: Specifies the name of the index to be deleted.
• ON table_name: Specifies the table from which to delete the index.

Syntax for deleting an index using the ALTER TABLE statement:

ALTER TABLE table_name DROP INDEX index_name;

• ALTER TABLE: Keyword used to modify the table structure.
• table_name: Specifies the name of the table to be modified.
• DROP INDEX: Clause used to delete an index.
• index_name: Specifies the name of the index to be deleted.

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

DROP INDEX idx_age ON employees;

Or use the ALTER TABLE statement:

ALTER TABLE employees DROP INDEX idx_age;

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

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

---

Unique Index

In MySQL, you can use the CREATE UNIQUE INDEX statement to create a unique index.

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

Creating an Index

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

• CREATE UNIQUE INDEX: Keyword combination 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 the table on which 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 typically numeric, text, or date.
• ASC and DESC (optional): Used to specify the sort order of the index. By default, indexes are sorted in ascending order (ASC).

Here is an example of creating a unique index: Suppose we have a table named employees with columns id and email. 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.

Modifying Table Structure to Add an Index

We can use the ALTER TABLE command to create a unique index.

The ALTER TABLE command allows you to modify an existing table structure, including adding new indexes.

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

• ALTER TABLE: Keyword used to modify the table structure.
• table_name: Specifies the name of the table to be modified.
• ADD CONSTRAINT: 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 typically numeric, text, or date.

Here is an example of using the ALTER TABLE command to create a unique index: Suppose we have a table named employees with columns id and email. 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.

Please 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 ensure that there are no duplicate values in the email column of the table.

Specifying Directly When Creating the Table

We can also use the UNIQUE keyword in the CREATE TABLE statement to create a unique index when creating the table.

This will define the unique index constraint at the time of table creation.

Syntax for creating a unique index in the CREATE TABLE 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 a composite index. The data types of these columns are typically numeric, text, or date.
• CONSTRAINT: Keyword used to add constraints.
• 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 names to be indexed.
• ASC and DESC (optional): Used to specify the sort order of the index. By default, indexes are sorted in ascending order (ASC).

Here is an example of creating a unique index when creating a table: Suppose we want to create a table named employees with columns id, name, and email. 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.

Please note that when using the UNIQUE keyword, the index name will be generated automatically. You can also specify the index name as needed.

---

Using ALTER Command to Add and Delete Indexes

There are four ways to add indexes to a table:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): This statement adds a primary key. The values in the primary key columns must be unique. The primary key column list can consist of one or more columns and cannot contain NULL values.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): This statement creates an index where the values 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 delete an index. Try the following example to delete an index:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

---

Using ALTER Command to Add and Delete Primary Keys

A primary key acts on columns (can be a single column or multiple columns as a composite 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 delete a primary key:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

---

Displaying Index Information

You can use the SHOW INDEX command to list the relevant index information of a table.

You can format the output information by adding G.

SHOW INDEX statement:

mysql> SHOW INDEX FROM table_nameG

• SHOW INDEX: Keyword used to display index information.
• FROM table_name: Specifies the name of the table to view index information.
• G: Formats the output information.

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