SQL - Indexes

SQL - Database Indexes

Database indexes are special data structures used to dramatically speed up data retrieval operations on a database table. While they are invisible to users, understanding how to create and manage them is one of the most important skills for improving database performance.

The classic analogy is the index at the back of a book. Instead of reading the entire book to find a topic (a ‘full table scan’), you look up the topic in the index, which points you directly to the right page number (the data’s location on disk).

The Trade-Off: Faster Reads, Slower Writes

Indexes are not free. They come with a cost:

• Faster SELECTs: Queries with WHERE, JOIN, and ORDER BY clauses on indexed columns are significantly faster.
• Slower Writes (INSERT, UPDATE, DELETE): When you modify data in an indexed column, the database must not only update the table but also update the index structure. This adds overhead.
• Storage Space: Indexes consume disk space.

The key is to create indexes strategically on columns that are frequently used for searching, filtering, and sorting.

Creating and Dropping Indexes

The CREATE INDEX Statement

The syntax to create a basic index is straightforward.

CREATE INDEX index_name ON table_name (column1, column2, ...);

The DROP INDEX Statement

Dropping an unused or ineffective index is just as important for performance.

-- Syntax for PostgreSQL
DROP INDEX index_name;

-- Syntax for MySQL
DROP INDEX index_name ON table_name;

Common Types of Indexes

1. Single-Column Index

The most basic type, created on a single column. Ideal for columns frequently used in WHERE clauses.

CREATE INDEX idx_users_email ON Users (Email);

2. Composite (Multi-Column) Index

An index on two or more columns. Very powerful for queries that filter on multiple columns. The order of columns matters! An index on (LastName, FirstName) is most effective for queries filtering on LastName or on both LastName and FirstName.

CREATE INDEX idx_employees_dept_salary ON Employees (Department, Salary);

3. Unique Index

Ensures that all values in the indexed column(s) are unique. It serves both for performance and for data integrity. PRIMARY KEY and UNIQUE constraints automatically create a unique index behind the scenes.

CREATE UNIQUE INDEX uq_products_sku ON Products (SKU);

4. Implicit Indexes

As mentioned, these are automatically created by the database when you define a PRIMARY KEY or UNIQUE constraint. You don’t need to create them manually.

How to Verify if an Index is Being Used

Creating an index doesn’t guarantee the database will use it. The database’s query planner makes a cost-based decision on the best way to execute a query. To check its decision, you must use the EXPLAIN (or EXPLAIN ANALYZE) command.

EXPLAIN SELECT * FROM Users WHERE Email = 'example@test.com';

The output will show the query plan. Look for terms like ‘Index Scan’ or ‘Index Seek’, which indicate your index is being used. If you see ‘Sequential Scan’ or ‘Table Scan’, it means the database is reading the whole table, and your index might not be effective for that specific query.

Best Practices: When to Use and Avoid Indexes

Good Candidates for Indexes

• Foreign Key Columns: Always index your foreign keys to speed up joins.
• WHERE Clause Columns: Any column frequently used to filter data.
• ORDER BY Columns: Indexing columns used in ORDER BY can prevent costly sorting operations.
• Columns with high cardinality (many unique values).

When to Avoid or Be Cautious

• Small Tables: The overhead of an index is not worth it on very small tables; a full scan is already fast enough.
• Tables with Frequent, Large Batch Operations: If you are bulk-loading or updating millions of rows, it can be faster to drop the indexes, perform the operation, and then recreate them.
• Columns with Low Cardinality: An index on a boolean IsActive column is often not helpful, as the planner might decide a table scan is cheaper than using the index.
• Frequently Modified Columns: Columns that are updated very frequently can cause significant index maintenance overhead.