SQL Create Index (CREATE INDEX statement)

In this tutorial, you will learn how to create indexes on tables to improve database performance.

What is an index?

An index is a data structure associated with a table that provides fast access to rows in the table based on the values of one or more columns (index keys).

Assuming there is acustomersSuppose you have a table in your database and you want to find all customers whose names start with the letter A using the following statement.

SELECT cust_id, cust_name, address FROM customers　
WHERE cust_name LIKE 'A%';

To find such customers, the server must becustomersThe table is scanned row by row and the content of the name column is checked. Although it works normally for tables with only a few rows, imagine if the table contains one million rows, then it may take a long time to answer the query. In this case, you can speed up the processing speed by applying an index to the table.

Create index

You can create an index using the following CREATE INDEX statement:

CREATE INDEX index_name ON table_name (column_name);

For example, to create an index on thenameIndexes can be created on columns using:

CREATE INDEX cust_name_idx ON customers (cust_name);

By default, indexes will allow duplicate entries and sort entries in ascending order. To require unique index entries, add the keyword UNIQUE after CREATE, as shown below:

CREATE UNIQUE INDEX cust_name_idx　
ON customers (cust_name);

In MySQL, you can view the available indexes on a specific table as follows:

mysql> SHOW INDEXES FROM customers \G

Create multi-column indexes

You can also build multi-column indexes. For example, assume you have a named table in your databaseof userswith columnsfirst_nameandlast_nameAnd if you frequently access records using these columns, you can create an index on two columns together to improve performance, as shown in the following figure:

CREATE INDEX user_name_idx ON users (first_name, last_name);

The disadvantages of indexes

Indexes should be created cautiously. Because every time a row is added, updated, or deleted in the table, all indexes on the table must be modified. Therefore, the more indexes you have, the more work the server needs to do, which ultimately leads to a decrease in performance.

The following are some basic guidelines that can be followed when creating indexes:

• Create indexes for columns that you frequently use to retrieve data.

• Do not create indexes for columns that are never used as retrieval keys.

• Index columns used to join to improve join performance.

• Avoid columns containing too many NULL values.

Similarly, small tables do not need indexes because in the case of small tables, the server scanning the table is usually faster than looking at the index first.

Index Deletion

You can use the following statement to delete indexes that are no longer needed.

DROP INDEX index_name ON table_name;

The following statement will remove the index fromcustomersTable Index Deletioncust_name_idx.

DROP INDEX cust_name_idx ON customers;

In addition, ifDelete Table:Then all related indexes will also be deleted.