Schema & Design
Database Indexes
A database index is a physical data structure that improves the speed of data retrieval operations on a table at the cost of additional storage and write.
Overview
A database index is a physical data structure that improves the speed of data retrieval operations on a table at the cost of additional storage and write overhead. Indexes are created on one or more columns of a table and maintain a sorted representation of the indexed values, allowing the database engine to locate rows without scanning the entire table. The SQL standard introduced indexes in SQL-86, primarily through the CREATE INDEX statement, though the standard leaves many implementation details to the vendor. Indexes are transparent to SQL queries: the optimizer decides whether to use an index based on statistics and query predicates. Common index types include B-tree, hash, bitmap, and full-text, but B-tree is the default in most relational systems. While indexes are essential for performance, they are not free: each index consumes disk space and must be updated on INSERT, UPDATE, and DELETE, which can slow down write-heavy workloads. Proper index design is a core aspect of schema tuning and requires understanding query patterns and data distribution.
Syntax and usage
The SQL standard defines CREATE INDEX as: CREATE [UNIQUE] INDEX index_name ON table_name (column_name [, ...]); Additional clauses like ASC/DESC, WHERE (partial indexes), and INCLUDE (covering indexes) are vendor-specific. For example, PostgreSQL supports CREATE INDEX idx_name ON table (col) WHERE condition; and CREATE INDEX idx_name ON table (col) INCLUDE (other_col);. MySQL supports CREATE INDEX idx_name ON table (col) USING BTREE; and CREATE FULLTEXT INDEX. SQL Server allows CREATE INDEX idx_name ON table (col) INCLUDE (other_col) WHERE condition;. Oracle supports function-based indexes: CREATE INDEX idx_name ON table (UPPER(col));. To drop an index, the standard syntax is DROP INDEX index_name; but some systems require DROP INDEX index_name ON table_name; (e.g., MySQL). Indexes can also be created implicitly by constraints: PRIMARY KEY and UNIQUE constraints automatically create unique indexes in most systems. Always consult vendor documentation for exact syntax and available options.
How it works
Most relational databases implement indexes as B-tree structures, which keep data sorted and allow logarithmic-time search, insertion, and deletion. Each index entry contains the indexed column values and a pointer to the corresponding row (e.g., a row ID or primary key). When a query includes a predicate on an indexed column, the optimizer may choose an index scan or index seek instead of a full table scan. For a B-tree, the engine traverses from the root to a leaf, narrowing the search range at each level. If the index covers all columns needed by the query (a covering index), the engine can answer the query entirely from the index, avoiding table access. Hash indexes, used in some systems, provide constant-time equality lookups but do not support range queries. Bitmap indexes are efficient for low-cardinality columns and are common in data warehousing. The optimizer uses statistics about data distribution to estimate the cost of using an index versus a table scan; stale or missing statistics can lead to poor plan choices. Indexes also enforce uniqueness when declared as unique, and they can support referential integrity checks for foreign keys.
Examples
Create a simple B-tree index on the last_name column of the employees table: CREATE INDEX idx_employees_last_name ON employees (last_name); This index can speed up queries like SELECT * FROM employees WHERE last_name = 'Smith';. To create a unique index that enforces uniqueness, use: CREATE UNIQUE INDEX idx_employees_email ON employees (email);. A composite index on multiple columns is created with: CREATE INDEX idx_employees_dept_hired ON employees (department_id, hire_date);. This index can support queries that filter on department_id alone or on both department_id and hire_date, but not on hire_date alone. A partial index (PostgreSQL, SQLite) can index only a subset of rows: CREATE INDEX idx_active_employees ON employees (last_name) WHERE active = true;. To drop an index: DROP INDEX idx_employees_last_name; (standard) or DROP INDEX idx_employees_last_name ON employees; (MySQL). In SQL Server, you can include additional columns to make a covering index: CREATE INDEX idx_employees_covering ON employees (last_name) INCLUDE (first_name, salary);.
Common pitfalls
Over-indexing is a frequent mistake: each index adds write overhead and storage, and too many indexes can degrade INSERT, UPDATE, and DELETE performance. Indexes on low-cardinality columns (e.g., gender) are rarely useful for equality searches because they do not narrow the result set enough. Leading wildcard searches like LIKE '%term' cannot use a standard B-tree index. Functions or expressions on indexed columns in the WHERE clause (e.g., WHERE UPPER(last_name) = 'SMITH') prevent index usage unless a function-based index is created. Implicit type conversions can also disable index usage. Composite indexes are only usable when the query filters on a prefix of the indexed columns; an index on (a, b) cannot be used for a query that filters only on b. Indexes may become fragmented over time, requiring maintenance such as REBUILD or REORGANIZE. In some databases, indexes are not automatically updated for certain operations, or they may be ignored due to outdated statistics. Finally, unique indexes can cause unexpected failures if duplicate data exists during creation.
Best practices
Index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses. For composite indexes, order columns by selectivity, placing the most selective columns first, but also consider query patterns. Use covering indexes to avoid table lookups for critical queries. Regularly review index usage statistics and drop unused or redundant indexes. Avoid indexing columns that are frequently updated unless necessary. Consider partial indexes for queries that target a small subset of rows. Use included columns (SQL Server) or INCLUDE clause (PostgreSQL) to add non-key columns to an index without making them part of the key. Monitor index fragmentation and rebuild or reorganize as needed. Keep statistics up to date to help the optimizer make informed decisions. Test index changes in a staging environment before deploying to production. Remember that indexes are a trade-off: they speed up reads but slow down writes, so balance based on workload. Document index rationale for maintainability.
Frequently asked questions
Q: Does an index always speed up queries? A: No. Indexes help when the query filters or joins on indexed columns and the optimizer chooses to use the index. For queries that return a large fraction of the table, a full table scan may be faster. Also, indexes add overhead to data modification statements. Q: What is the difference between a clustered and a non-clustered index? A: A clustered index determines the physical order of rows in the table; there can be only one per table. A non-clustered index is a separate structure that points to the rows. Not all databases support clustered indexes (e.g., PostgreSQL does not). Q: Can I create an index on a view? A: Some databases support indexed views (materialized views with indexes), but the SQL standard does not define them. Vendor-specific syntax and restrictions apply. Always check your database documentation.