SQL Index : The Complete Developer’s Guide

1. Introduction

Every developer has encountered a query that works perfectly on a small dataset but slows to a crawl when the table grows to millions of rows. In most cases, the root cause is not a poorly written query — it is the absence of a well-placed index. Understanding SQL indexing, therefore, is one of the most impactful skills a developer can build.

To begin with, an index is a separate data structure that the database engine maintains alongside your table. Much like the index at the back of a textbook, it allows the engine to jump directly to the relevant pages instead of reading every single row. As a result, queries that would otherwise scan millions of records can return results in milliseconds.

However, indexes are not a magic solution. While they dramatically speed up read queries, they also add overhead to write operations and consume additional disk space. Consequently, knowing when to add an index — and which type to use — is just as important as knowing how to create one.

In this guide, we walk through every major index type, explain how each one works internally, show you exactly how to create and manage them, and — most importantly — teach you how to use EXPLAIN to verify that the database is actually using your indexes. By the end, you will have a complete, practical toolkit for SQL indexing.

2. How Indexes Work Internally

Before diving into the different types of indexes, it is important to first understand the problem they solve. When you run a query like SELECT * FROM employees WHERE email = ‘alice@example.com’, the database engine has two options. First, it can perform a full table scan — reading every single row from disk until it finds a match. Second, if an index exists on the email column, it can instead look up the value in the index structure and jump directly to the matching row.

The difference in performance is enormous. For a table with 10 million rows, a full table scan reads 10 million rows. A B-Tree index lookup, by contrast, traverses a tree of roughly log2(10,000,000) ≈ 23 levels and reads just a handful of pages. That is the fundamental power of indexing.

2.1 — The B-Tree: The Engine Behind Most Indexes

The B-Tree (Balanced Tree) is the data structure that underpins the majority of SQL indexes in PostgreSQL, MySQL, SQL Server, and Oracle. To understand why it is so effective, consider how it is structured.

At the top sits the root node, which contains key values and pointers to child nodes. Below the root are internal nodes that further divide the key space. Finally, at the bottom sit the leaf nodes, which contain the actual indexed values along with pointers (row IDs or physical addresses) to the corresponding rows in the table.

Because the tree is always balanced — meaning all leaf nodes are at the same depth — every search takes the same number of steps regardless of which value you are looking for. Furthermore, the leaf nodes are linked together in a doubly linked list, which makes range queries like BETWEEN or > and < extremely efficient: the engine finds the starting point and then simply walks the leaf chain.

2.2 — Full Table Scan vs Index Scan: A Real Example

-- Table: employees with 5 million rows
-- No index on email column yet
-- This query triggers a FULL TABLE SCAN
SELECT emp_id, name, email
FROM   employees
WHERE  email = 'alice@example.com';
-- Check the execution plan (PostgreSQL)
EXPLAIN ANALYZE
SELECT emp_id, name, email
FROM   employees
WHERE  email = 'alice@example.com';
-- Output (no index):
-- Seq Scan on employees  (cost=0.00..98421.00 rows=1 width=60)
--                        (actual time=1823.412..1823.414 rows=1)
-- Execution Time: 1824.101 ms   <- 1.8 seconds for one row!

3. Creating Your First Index

Now that we understand why indexes matter, let us look at the syntax for creating them. The basic CREATE INDEX statement is straightforward and works across all major databases with minor variations.

-- Basic syntax
CREATE INDEX index_name ON table_name (column_name);
-- Create an index on the email column of employees
CREATE INDEX idx_employees_email
ON employees (email);
-- After adding the index, run the same query again
EXPLAIN ANALYZE
SELECT emp_id, name, email
FROM   employees
WHERE  email = 'alice@example.com';
-- Output (with index):
-- Index Scan using idx_employees_email on employees
--   (cost=0.56..8.58 rows=1 width=60)
--   (actual time=0.041..0.043 rows=1)
-- Execution Time: 0.112 ms   <- from 1824ms to 0.1ms!

OUTPUT: Performance improvement after adding an index

3.1 — Dropping and Rebuilding Indexes

-- Drop an index (PostgreSQL / MySQL)
DROP INDEX idx_employees_email;
-- Drop an index (SQL Server)
DROP INDEX idx_employees_email ON employees;
-- Rebuild an index (PostgreSQL) — useful after heavy INSERT/UPDATE/DELETE
REINDEX INDEX idx_employees_email;
-- Rebuild all indexes on a table (PostgreSQL)
REINDEX TABLE employees;
-- Rebuild index online without locking the table (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_employees_email;

4. Types of SQL Indexes

Not all indexes are the same. In fact, different index types are designed for different data access patterns. Consequently, choosing the right type for each use case is just as important as deciding whether to add an index at all.

4.1 — Single-Column Index

The single-column index is the simplest and most commonly used type. As the name suggests, it indexes one column and is ideally suited for queries that filter, sort, or join on that specific column. For instance, if you frequently search employees by their department_id, a single-column index on department_id will significantly speed up those queries.

-- Single-column index on department_id
CREATE INDEX idx_emp_dept
ON employees (department_id);
-- This query will now use the index
SELECT name, salary
FROM   employees
WHERE  department_id = 10;
-- This query will also use the index for sorting
SELECT name, salary
FROM   employees
ORDER BY department_id;

4.2 — Composite Index (Multi-Column Index)

A composite index, on the other hand, spans two or more columns. It is particularly useful when queries filter on multiple columns together. However, there is one critical rule to understand: the leading column rule. A composite index on (last_name, first_name) can be used for queries that filter on last_name alone, or on both last_name AND first_name together. Nevertheless, it cannot be used for queries that filter on first_name alone.

-- Composite index on (last_name, first_name)
CREATE INDEX idx_emp_name
ON employees (last_name, first_name);
-- Query 1: Uses the index (last_name is the leading column)
SELECT * FROM employees WHERE last_name = 'Smith';
-- Query 2: Also uses the index (both leading columns present)
SELECT * FROM employees
WHERE last_name = 'Smith' AND first_name = 'John';
-- Query 3: Does NOT use the index (skips the leading column)
SELECT * FROM employees WHERE first_name = 'John';
-- A full table scan is performed instead

4.3 — Unique Index

A unique index serves a dual purpose: it speeds up queries and simultaneously enforces data integrity by preventing duplicate values in the indexed column. In practice, when you declare a PRIMARY KEY or a UNIQUE constraint on a column, the database automatically creates a unique index behind the scenes. Additionally, you can create one explicitly for any column that must contain unique values.

-- Explicit unique index
CREATE UNIQUE INDEX idx_emp_email_unique
ON employees (email);
-- This will succeed
INSERT INTO employees (name, email) VALUES ('Alice', 'alice@co.com');
-- This will FAIL with a duplicate key error
INSERT INTO employees (name, email) VALUES ('Bob',   'alice@co.com');
-- ERROR: duplicate key value violates unique constraint
-- Unique index on multiple columns (combination must be unique)
CREATE UNIQUE INDEX idx_emp_dept_badge
ON employees (department_id, badge_number);

4.4 — Partial Index (Filtered Index)

A partial index is a highly efficient specialisation that indexes only the rows satisfying a specified condition. This is especially valuable when you frequently query a small, well-defined subset of a large table. For example, if your orders table has 50 million rows but you only ever query the 100,000 active orders, indexing all 50 million rows wastes space and slows down writes. Instead, a partial index on active orders is much smaller and faster.

-- Instead of indexing all 50M orders, index only active ones
CREATE INDEX idx_orders_active
ON orders (customer_id)
WHERE status = 'active';
-- This query uses the partial index (matches the WHERE condition)
SELECT order_id, total
FROM   orders
WHERE  status = 'active'
AND    customer_id = 12345;
-- This query does NOT use the partial index (status != 'active')
SELECT order_id, total
FROM   orders
WHERE  status = 'completed'
AND    customer_id = 12345;
-- Falls back to a full table scan or a different index

4.5 — Full-Text Index

Standard B-Tree indexes are not suitable for searching within long text fields. If you need to search for a word or phrase inside a column like article_body or product_description, a full-text index is the right tool. Unlike a B-Tree index, a full-text index tokenises the text into individual words and builds an inverted index — a structure that maps each word to the rows containing it.

-- PostgreSQL: Full-text index using GIN
CREATE INDEX idx_articles_fts
ON articles USING GIN (to_tsvector('english', body));
-- Query using full-text search
SELECT title, body
FROM   articles
WHERE  to_tsvector('english', body) @@ to_tsquery('database & indexing');
-- MySQL: Full-text index
CREATE FULLTEXT INDEX idx_products_fts
ON products (product_name, description);
-- MySQL full-text search query
SELECT product_name
FROM   products
WHERE  MATCH(product_name, description) AGAINST ('wireless headphones');

4.6 — Covering Index (Index-Only Scan)

A covering index is not a distinct index type but rather a design strategy where the index contains all the columns a query needs. As a result, the database engine can answer the entire query from the index alone, without ever touching the main table. This technique, known as an Index-Only Scan, is one of the most powerful query optimisations available.

-- Query: find name and salary for all employees in dept 10
SELECT name, salary
FROM   employees
WHERE  department_id = 10;
-- Standard index: only covers the filter column
-- Engine must read index, then fetch name+salary from table (extra I/O)
CREATE INDEX idx_emp_dept_standard
ON employees (department_id);
-- Covering index: includes all columns the query needs
-- Engine answers entirely from the index -- no table access needed
CREATE INDEX idx_emp_dept_covering
ON employees (department_id)
INCLUDE (name, salary);   -- PostgreSQL / SQL Server syntax
-- MySQL equivalent (list all columns in the index)
CREATE INDEX idx_emp_dept_covering
ON employees (department_id, name, salary);
-- EXPLAIN now shows: Index Only Scan (fastest possible read)
EXPLAIN SELECT name, salary FROM employees WHERE department_id = 10;
-- Index Only Scan using idx_emp_dept_covering

4.7 — Clustered vs Non-Clustered Index

There is an important distinction between clustered and non-clustered indexes that every developer must understand. A clustered index determines the physical order of rows on disk — the table itself is sorted according to the index key. Consequently, each table can have only one clustered index. In SQL Server and MySQL InnoDB, the PRIMARY KEY is the clustered index by default.

A non-clustered index, by contrast, is a separate structure that contains the indexed key values and pointers back to the actual rows. Because it does not affect the physical row order, a table can have many non-clustered indexes. However, reading data through a non-clustered index requires an extra lookup — first into the index, then back to the table row.

-- SQL Server: explicit clustered index
CREATE CLUSTERED INDEX idx_emp_id_clustered
ON employees (emp_id);
-- SQL Server: non-clustered index
CREATE NONCLUSTERED INDEX idx_emp_email_nc
ON employees (email);
-- PostgreSQL: all indexes are non-clustered by default
-- Use CLUSTER command to physically reorder the table once
CLUSTER employees USING idx_emp_dept;
-- Note: CLUSTER is a one-time operation -- new inserts are unordered

5. When to Use (and Avoid) Indexes

Although indexes dramatically improve read performance, they are not free. In fact, every index you add comes with real costs that must be carefully weighed. Understanding both when to add indexes and when to hold back is, therefore, essential for maintaining a well-performing database.

5.1 — When Indexes Help

5.2 — When to Avoid Indexes

6. Using EXPLAIN to Verify Index Usage

Creating an index does not guarantee it will be used. The query planner makes its own decision based on table statistics, data distribution, and estimated costs. Therefore, it is essential to use EXPLAIN (or EXPLAIN ANALYZE) to verify that your indexes are actually being consulted.

6.1 — Reading EXPLAIN Output

-- PostgreSQL: EXPLAIN ANALYZE gives actual runtime stats
EXPLAIN ANALYZE
SELECT name, salary
FROM   employees
WHERE  department_id = 10
AND    salary > 80000;
-- Sample output:
-- Index Scan using idx_emp_dept on employees
--   (cost=0.56..312.45 rows=87 width=34)
--   (actual time=0.043..1.214 rows=87 loops=1)
--   Index Cond: (department_id = 10)
--   Filter: (salary > 80000)
--   Rows Removed by Filter: 143
-- Planning Time:  0.312 ms
-- Execution Time: 1.389 ms

OUTPUT: Key signals in EXPLAIN output and what they mean

6.2 — Why the Planner Might Ignore Your Index

-- Case 1: Function on indexed column breaks index usage
-- BAD: UPPER() prevents index use on email
SELECT * FROM employees WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
-- Seq Scan — index on email is NOT used
-- GOOD: Use a function-based index instead
CREATE INDEX idx_emp_email_upper ON employees (UPPER(email));
SELECT * FROM employees WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';
-- Index Scan on idx_emp_email_upper — index IS used
-- Case 2: Leading wildcard prevents B-Tree index use
-- BAD: Leading % means the engine cannot use the B-Tree
SELECT * FROM employees WHERE name LIKE '%Smith';
-- Seq Scan — cannot use a standard B-Tree index
-- GOOD: Trailing wildcard CAN use a B-Tree index
SELECT * FROM employees WHERE name LIKE 'Sm%';
-- Index Scan — engine uses B-Tree index on name
-- Case 3: Implicit type conversion prevents index use
-- BAD: emp_id is INT but compared to a string
SELECT * FROM employees WHERE emp_id = '12345';
-- Seq Scan in some databases due to implicit cast
-- GOOD: Match the data type exactly
SELECT * FROM employees WHERE emp_id = 12345;
-- Index Scan

7. Composite Index Design Best Practices

Composite indexes are among the most powerful tools in SQL performance tuning. Nevertheless, they are also among the most misunderstood. Following a few key design principles will help you get the maximum benefit from every composite index you create.

7.1 — Column Order Matters: Most Selective First

-- Query pattern: filter on status + sort by created_at
SELECT order_id, total, created_at
FROM   orders
WHERE  status = 'pending'
ORDER  BY created_at DESC;
-- Option A: status first (more selective if few 'pending' rows)
CREATE INDEX idx_orders_status_date
ON orders (status, created_at DESC);
-- Option B: created_at first (better for range-heavy queries)
CREATE INDEX idx_orders_date_status
ON orders (created_at DESC, status);
-- Option A is preferred here because filtering by status first
-- dramatically reduces the rows before the sort step.
-- In contrast, Option B would index by date first, offering
-- less benefit when filtering for a specific status.

7.2 — Equality Columns Before Range Columns

A critical rule for composite index design is to always place equality filter columns before range filter columns. The reason is that once a range condition is applied (such as >, <, or BETWEEN), the index cannot be used to filter subsequent columns — the engine must scan all matching range rows for the remaining conditions.

-- Query: filter by department (equality) AND salary range
SELECT name, salary
FROM   employees
WHERE  department_id = 10          -- equality condition
AND    salary BETWEEN 60000 AND 90000;  -- range condition
-- GOOD: equality column (department_id) first
CREATE INDEX idx_emp_dept_salary
ON employees (department_id, salary);
-- Engine uses department_id to narrow down, then scans salary range
-- BAD: range column (salary) first
CREATE INDEX idx_emp_salary_dept
ON employees (salary, department_id);
-- Engine must scan the entire salary range, then filter by department
-- Much less efficient for this query pattern

7.3 — Include Frequently Selected Columns (Covering Index)

-- Frequently run report query
SELECT emp_id, name, hire_date
FROM   employees
WHERE  department_id = 10
AND    status = 'active';
-- Without covering index: Index Scan + table fetch for name, hire_date
CREATE INDEX idx_emp_dept_status
ON employees (department_id, status);
-- With covering index: Index Only Scan -- no table access at all
CREATE INDEX idx_emp_dept_status_covering
ON employees (department_id, status)
INCLUDE (emp_id, name, hire_date);   -- PostgreSQL / SQL Server
EXPLAIN SELECT emp_id, name, hire_date
FROM employees WHERE department_id = 10 AND status = 'active';
-- Result: Index Only Scan using idx_emp_dept_status_covering

8. Index Maintenance and Monitoring

Creating an index is not a one-time task. Over time, as rows are inserted, updated, and deleted, indexes become fragmented and their statistics grow stale. Consequently, regular index maintenance is essential to sustain the performance gains they provide.

8.1 — Finding Unused Indexes

-- PostgreSQL: find indexes that have never been used
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan     AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
AND    schemaname = 'public'
ORDER  BY pg_relation_size(indexrelid) DESC;
-- SQL Server: find unused indexes
SELECT
    OBJECT_NAME(i.object_id)        AS table_name,
    i.name                           AS index_name,
    ius.user_seeks + ius.user_scans  AS total_reads,
    ius.user_updates                 AS total_writes
FROM   sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius
    ON  i.object_id  = ius.object_id
    AND i.index_id   = ius.index_id
WHERE  ius.user_seeks + ius.user_scans = 0
OR     ius.user_seeks IS NULL;

8.2 — Detecting Index Fragmentation

-- PostgreSQL: check index bloat using pgstattuple extension
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    (pgstattuple(indexrelid)).dead_tuple_percent AS dead_pct
FROM   pg_stat_user_indexes
WHERE  schemaname = 'public'
ORDER  BY dead_pct DESC;
-- If dead_pct > 20%, the index should be rebuilt
-- Rebuild the fragmented index (PostgreSQL)
REINDEX INDEX CONCURRENTLY idx_employees_email;
-- SQL Server: check fragmentation
SELECT index_id, avg_fragmentation_in_percent
FROM   sys.dm_db_index_physical_stats(
    DB_ID(), OBJECT_ID('employees'), NULL, NULL, 'SAMPLED'
);
-- < 10% fragmentation: no action needed
-- 10-30% fragmentation: ALTER INDEX ... REORGANIZE
-- > 30% fragmentation:  ALTER INDEX ... REBUILD

8.3 — Checking Index Size

-- PostgreSQL: size of each index on a table
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  tablename = 'employees'
ORDER  BY pg_relation_size(indexrelid) DESC;
-- MySQL: index statistics
SHOW INDEX FROM employees;
-- MySQL: table and index sizes
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2)   AS data_mb,
    ROUND(index_length / 1024 / 1024, 2)  AS index_mb
FROM   information_schema.tables
WHERE  table_schema = 'your_database'
AND    table_name   = 'employees';

9. Common Indexing Mistakes and How to Avoid Them

Even experienced developers make indexing mistakes that silently degrade performance. The following table summarises the most common pitfalls along with the correct approach, so that you can avoid them in your own work.

10. Quick Reference — Index Cheat Sheet

11. Conclusion

SQL indexing is, without doubt, one of the highest-leverage skills in database engineering. A single well-placed index can transform a query that takes two minutes into one that completes in under a millisecond. Moreover, understanding the different index types means you can choose the right tool for every situation — rather than relying on trial and error.

To summarise the key principles: first, always identify which columns are used in WHERE, JOIN, ORDER BY, and GROUP BY clauses before adding any index. Second, use composite indexes thoughtfully, placing equality columns before range columns and the most selective column first. Third, leverage covering indexes for high-frequency reporting queries to eliminate table access entirely. Fourth and most importantly, always use EXPLAIN ANALYZE to confirm that the query planner is actually using your index — because creating an index and having it used are two very different things.

Finally, remember that index maintenance is an ongoing responsibility. Unused indexes silently degrade write performance. Fragmented indexes degrade read performance. Consequently, regularly monitoring index usage statistics and rebuilding or dropping indexes as data patterns evolve is just as valuable as the initial design.

With the knowledge in this guide — covering index types, creation syntax, the EXPLAIN tool, composite index design, and maintenance patterns — you now have everything you need to build a fast, well-indexed SQL database that performs reliably at any scale.

Happy Querying!