A SQL index works much like the index at the back of a book. Instead of searching every page—or, in this case, every row—the database uses the index to locate the requested data more efficiently.
SQL indexes are created on one or more columns that are frequently used to search, sort, or join data. For example, the following statement creates an index on the Email column in the users table in Microsoft SQL Server:
CREATE INDEX ix_users_email ON dbo.users (Email);
MySQL and PostgreSQL use the same basic syntax, normally without SQL Server's dbo schema qualifier unless an equivalent schema has been defined.
Unique indexes
For an index that requires unique values, use a unique index. Notice that the prefix on the index name changes from ix to ux. These prefixes are not required, but using them makes the index type easier to identify in a long list of indexes.
CREATE UNIQUE INDEX ux_users_email ON dbo.users (Email);
Composite indexes
A composite index contains more than one column:
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);
Column order matters. This index is organized by customer_id first and then by order_date within each customer. It can efficiently support a query that filters by customer_id, or by both customer_id and order_date. A query that filters only by order_date will usually require a separate index that begins with order_date.
An index stores its key values in an organized structure, allowing the database to find matching records without scanning the entire table. It can significantly improve the performance of SELECT queries, particularly when indexed columns appear in WHERE, JOIN, or ORDER BY clauses.
However, indexes also have a cost. They require additional storage, and the database must update them whenever records are inserted, modified, or deleted. As a result, having too many indexes can reduce write performance.
When used thoughtfully, indexes are especially valuable for reporting, searching, and general data retrieval. The goal is to index columns that are queried often while avoiding unnecessary indexes that add maintenance overhead. That overhead may be experienced in the call center or other departments where data entry is performed.
Indexes are one item a DBA would check when end users complain about service degradation.