SQL Create Index

Previous Page Next Page

What is Index?

An index is a data structure linked to a table that allows quick access to rows based on the values in one or more columns (the index key).

For example, if you have a customers table in your database and you want to find all customers whose names start with the letter A, you would use the following statement.

Example

Try this code »

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

To locate such customers, the server needs to scan each row in the customers table and check the name column. This method works well for a table with a few rows, but imagine how long it would take if the table had millions of rows. In this case, you can speed up the process by applying indexes to the table.

Creating an Index

You can create indexes using the CREATE INDEX statement:

CREATE INDEX index_name ON table_name (column_name);

For instance, to create an index on the name column in the customers table, you could use:

Example

Try this code »

CREATE INDEX cust_name_idx ON customers (cust_name);

By default, the index allows duplicate entries and sorts them in ascending order. To ensure the index entries are unique, add the keyword UNIQUE after CREATE, like this:

Example

Try this code »

CREATE UNIQUE INDEX cust_name_idx 
ON customers (cust_name);

In MySQL, you can view the indexes available on a specific table like this:

mysql> SHOW INDEXES FROM customers \G

Tip: End a SQL statement with \G instead of ; to show the result vertically rather than in the usual tabular format if the results are too wide for the current window.

Creating Multi-column Indexes

You can also create indexes that cover multiple columns. For example, if you have a table in your database named users with the columns first_name and last_name, and you often access user records using these columns, you can build an index on both columns together to enhance performance, like this:

CREATE INDEX user_name_idx ON users (first_name, last_name);

Tip: Think of a database index like the index section of a book, which helps you quickly find or locate a specific topic within the book.

The Downside of Indexes

Indexes should be created carefully. This is because every time a row is added, updated, or deleted from a table, all indexes on that table need to be updated. Therefore, having more indexes means more work for the server, which can result in slower performance.

Here are some basic guidelines to follow when creating indexes:

Index columns that you frequently use to retrieve data.
Avoid creating indexes for columns that you never use as retrieval keys.
Index columns used in joins to enhance join performance.
Steer clear of columns that contain many NULL values.

Additionally, small tables typically don't need indexes, because for small tables, it's usually faster for the server to scan the table rather than check the index first.

Note: Many database systems, such as MySQL, SQL Server, etc., automatically create indexes for PRIMARY KEY and UNIQUE columns when the table is created.

Drop Indexes

You can remove indexes that are no longer needed using the following statement.

DROP INDEX index_name ON table_name;

The following statement removes the index cust_name_idx from the customers table.

Example

Try this code »

DROP INDEX cust_name_idx ON customers;

Furthermore, if you drop a table, all associated indexes are also removed.

Warning: Always thoroughly investigate before dropping an index. As a best practice, avoid creating or dropping indexes without careful consideration.

Previous Page Next Page