A database index is a data structure that improves the speed of data retrieval in the database. Indexes on a table consist of one or more columns of ordered data with links to specific rows in a table.
By matching the values in the index, the database management system can quickly retrieve the corresponding row without having to search every row in the table.
Tables are indexed on their primary key columns, and many database systems require an index on a foreign key column as well. It is also common practice to place indexes on columns that are likely to be queried often.
Index creation can vary from database to database, but in standard SQL it consists of:
- Using the
CREATE INDEXstatement, followed by a name for the index.
- Applying the
ONclause to the table name, followed by a list of the columns to be indexed.
In the example below, adding an index on the
region field of a
sales table looks like this:
CREATE INDEX sales_by_regionON sales (region);