❮ Mysql Insert Query Mysql Install ❯

MySQL Indexes

The establishment of MySQL indexes is crucial for efficient operation of MySQL. Indexes can significantly enhance the retrieval speed of MySQL.

For example, if MySQL with well-designed and utilized indexes is like a Lamborghini, then MySQL without indexes is akin to a human-powered tricycle.

Taking the index page of a Chinese dictionary as an example, we can quickly find the required characters using sorted directories (indexes) by pinyin, stroke, radical, etc.

Indexes can be single-column or composite. A single-column index contains only one column, and a table can have multiple single-column indexes, but this is not a composite index. A composite index includes 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 conditions in the WHERE clause).

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

All the above discusses the benefits of using indexes, but excessive use of indexes can lead to abuse. Therefore, indexes also have their drawbacks: although indexes greatly improve query speed, they can reduce the speed of updating tables, such as when performing INSERT, UPDATE, and DELETE operations. This is because when updating a table, MySQL not only saves data but also the index file.

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

Regular Index

Creating an Index

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

CREATE INDEX indexName ON table_name (column_name)

If it is a CHAR or VARCHAR type, the length can be less than the actual column length; if it is BLOB or TEXT type, you must specify the length.

Modifying Table Structure (Adding Index)

ALTER TABLE tableName ADD INDEX indexName(columnName)

Directly Specifying During Table Creation

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 regular index, but the values in the indexed column must be unique, although null values are allowed. For composite indexes, the combination of column values must be unique. It can be created in several ways:

Creating an Index

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

Modifying Table Structure

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

Directly Specifying During Table Creation

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

Adding and Removing Indexes Using ALTER Command

There are four ways to add an index to a table:

ALTER TABLE tblname ADD PRIMARY KEY (columnlist): This statement adds a primary key, meaning the index values must be unique and cannot be NULL.
ALTER TABLE tblname ADD UNIQUE indexname (column_list): This statement creates an index where the values must be unique (except for NULL, which can appear multiple times).
ALTER TABLE tblname ADD INDEX indexname (column_list): Adds a regular index where the index values can appear multiple times.
ALTER TABLE tblname ADD FULLTEXT indexname (column_list): This statement specifies a FULLTEXT index, used for full-text indexing.

Example of adding an index to a table:

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can also remove an index using the DROP clause in the ALTER command. Example of removing an index:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Adding and Removing Primary Keys Using ALTER Command

Primary keys apply to columns (a single column or multiple columns combined). When adding a primary key index, you need to ensure that the primary key is not null by default. 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 remove a primary key:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

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

Displaying Index Information

You can list the index information of a table using the SHOW INDEX command. You can format the output by adding \G. Example:

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

❮ Mysql Insert Query Mysql Install ❯