intermediatePerformance100% Free

SQL Database Indexing

Definition

A database index is a data structure that improves the speed of data retrieval operations by creating a sorted reference to one or more columns of a table.

Introduction to SQL Database Indexing

Indexes are the single most impactful performance optimization in SQL. Without an index, the database must scan every row in a table (full table scan). With the right index, it can jump directly to matching rows in microseconds. Understanding when and how to create indexes separates junior from senior database engineers.

Syntax

SQL
-- Create a simple index
CREATE INDEX idx_name ON table(column);

-- Create a composite index
CREATE INDEX idx_name ON table(col1, col2);

-- Create a unique index
CREATE UNIQUE INDEX idx_name ON table(column);

-- Drop an index
DROP INDEX idx_name;

Examples

1

Create Index on Frequently Queried Column

-- Without index: full table scan on 1M rows
-- With index: direct lookup in milliseconds
CREATE INDEX idx_employees_email
ON employees(email);

-- Now this query is instant:
SELECT * FROM employees WHERE email = 'john@example.com';

Creates a B-tree index on the email column. Queries filtering by email now skip the full table scan.

Try in Playground
2

Composite Index for Multi-Column Filters

-- Optimizes queries that filter on both columns
CREATE INDEX idx_emp_dept_salary
ON employees(department, salary);

-- This query uses the composite index efficiently:
SELECT name FROM employees
WHERE department = 'Engineering'
  AND salary > 80000;

Composite indexes work left-to-right. This index helps queries filtering on department alone, or department + salary, but NOT salary alone.

Try in Playground
3

Check if Index is Being Used (EXPLAIN)

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM employees WHERE email = 'john@example.com';

-- MySQL
EXPLAIN SELECT * FROM employees WHERE email = 'john@example.com';

EXPLAIN shows the query execution plan. Look for 'Index Scan' (good) vs 'Seq Scan' or 'Full Table Scan' (potentially slow).

Try in Playground

Common Mistakes

Over-indexing — too many indexes slow down INSERT/UPDATE/DELETE operations (indexes must be maintained)

Wrong column order in composite indexes — the most selective column should come first

Indexing low-cardinality columns (e.g., boolean fields) — provides minimal benefit

Not indexing foreign key columns — JOIN performance suffers dramatically without FK indexes

Using functions on indexed columns in WHERE: WHERE UPPER(name) = 'JOHN' cannot use an index on name

Frequently Asked Questions

What is a database index and how does it work?
A database index is a sorted data structure (typically a B-tree) that stores the values of one or more columns along with pointers to the full rows. When you query with WHERE, the database searches the index (like a book's index) to find matching row pointers, then fetches just those rows instead of scanning all rows.
When should you NOT use an index?
Avoid indexes on: small tables (full scan is faster), columns with very low cardinality (few unique values like boolean), columns that are never used in WHERE/JOIN/ORDER BY, and tables with frequent bulk INSERTs/UPDATEs where index maintenance overhead outweighs query benefits.
What is a composite index in SQL?
A composite (multi-column) index covers multiple columns: CREATE INDEX idx ON orders(customer_id, order_date). It is most useful when queries filter on multiple columns together. The leftmost column prefix rule means the index helps queries on (customer_id) or (customer_id, order_date) but not (order_date) alone.
What is a covering index?
A covering index includes all columns needed by a query, so the database can answer the query from the index alone without touching the actual table rows. This is the fastest possible index scenario: CREATE INDEX idx_covering ON orders(customer_id, status, amount) for SELECT status, amount FROM orders WHERE customer_id = 5;

Related SQL Topics

Query OptimizationJoinsWindow FunctionsTransactionsExplain Plan

Practice This in the SQL Playground

Write real queries, see live results, and master SQL Database Indexing hands-on. 100% free.

Open SQL PlaygroundBrowse All Lessons