Indexes are specialized data structures that organize a table ahead of time to improve the speed of database searches. This is accomplished through a balanced tree structure that allows the server to only search a subset of relevant rows instead of every row sequentially.
While an index organizes the values of an entire table, the organization is based on the value of a specific column. This column should ideally be frequently searched and have a varied set of data to best realize the improved performance of the sorting provided by the index. Columns that are not often used in queries should be avoided. Similarly, columns with many identical values, such as binary columns or columns with a high number of
NULL values, should not be indexed.
Indexes are best used on large tables where a small subset of the data is commonly searched. If instead, all the rows are frequently accessed (perhaps to calculate a sum), the extra organization of the index will be irrelevant. Also, on smaller tables the speed improvements will be less noticeable, as most rows may have to be searched anyways.
The index also needs to be maintained on each
DELETE event, possibly making these actions slower. Therefore, tables that feature frequent writes and large updates should avoid indexes.
As an index is built over time, it is possible for the data to be fragmented across a disk, lowering the performance. This can be avoided by removing and recreating the index, although the implementation differs by database version.
Indexes are commonly created alongside the table that it describes:
CREATE TABLE table_name (column_name datatype,column_name datatype,...INDEX index_name (index_column_name));
However, they can also be created for existing tables:
CREATE INDEX index_nameON table_name (column_name);
To create an index named
id_index for the frequently searched
student_id column in the
students table, use the following query:
CREATE INDEX id_indexON students (student_id);
Creating a Unique Index
Due to indexes performing better with more varied data, it is encouraged to enforce unique indexes that disallow duplicates. This works best with columns that happen to be the
PRIMARY KEY as well.
To create a unique index for the
student_id that does not allow duplicate values, use the following query:
CREATE UNIQUE INDEX unique_id_indexON students (student_id);
Removing an Index
The following query can be used to remove the original index placed on
DROP INDEX id_index;
- Anonymous contributors
Learn SQL on Codecademy
- Anonymous contributorsAnonymous contributors