Skip to main content

SQL Indexes - The Complete Guide

Ever wondered why some SQL queries feel like they run in milliseconds while others take minutes on the same table? The secret often lies in how well your database uses indexes. Let’s explore how SQL indexes work and how you can use them to make your queries fly

What are SQL Indexes?​

SQL Indexes are special database structures that dramatically speed up data retrieval operations. Think of them like an index in a book - instead of reading every page to find information, you can jump directly to the right page.

note

Key Characteristics of Indexes:

  • Speed Up Queries: Can make queries 10x, 100x, or even 1000x faster.

  • Cost of Storage: Require additional disk space to store the index structure.

  • Write Overhead: Slow down INSERT, UPDATE, and DELETE operations slightly.

  • Automatic Maintenance: Database automatically updates indexes when data changes.

success

The Phone Book Analogy:

Imagine searching for "John Smith" in a phone book:

Without Index (Full Table Scan): You'd have to read every single entry from page 1 to the end until you find John Smith. On a million-entry phone book, this is painfully slow.

With Index: The phone book is already sorted alphabetically (that's an index!). You can jump directly to the "S" section and find John Smith in seconds.

That's exactly what database indexes do - they organize data in a way that makes searches lightning-fast.

Real-World Impact: A query that takes 30 seconds on a million-row table without an index might complete in 0.01 seconds with the right index. That's a 3000x performance improvement!

info

How Indexes Work Under the Hood​

-- Without index: Database scans every row
SELECT * FROM employees WHERE employee_id = 12345;
-- Scans: 1, 2, 3, 4, ... 12345 (sequential search)

-- With index: Database jumps directly to the row
SELECT * FROM employees WHERE employee_id = 12345;
-- Jump directly to: 12345 (index lookup)

Index Structure (Simplified B-Tree):

                    [50]
                   /    \
              [25]        [75]
             /   \       /    \
         [10]  [40]  [60]   [90]

The database traverses this tree structure to find values quickly. For a million rows, it might only need to check 20-30 nodes instead of a million rows!

OperationWithout IndexWith IndexImprovement
Find by IDO(n) - LinearO(log n) - LogarithmicExponential
Range searchO(n)O(log n + k)Significant
SortO(n log n)O(n) or O(1)Major

Types of Indexes​

Index TypeDescriptionBest Use Case
Clustered IndexDetermines the physical order of data in the table. Each table can have only one.Primary key columns.
Non-Clustered IndexA separate structure that points to table data.Columns used in WHERE, JOIN, ORDER BY.
Composite IndexIndex on multiple columns.Queries filtering on multiple conditions.
Unique IndexPrevents duplicate values in a column.Email IDs, Usernames, etc.
Covering IndexIncludes all columns a query needs.Read-heavy analytical queries.
Partial IndexIndexes a subset of rows.Filtering on frequently used conditions (e.g., active users).
Full-Text IndexOptimized for text search.Searching within text or document fields.

Creating and Managing Indexes​

tip

Creating Indexes - Syntax Variations

-- Basic syntax
CREATE INDEX index_name ON table_name(column_name);

-- Multiple columns
CREATE INDEX idx_name ON table_name(col1, col2, col3);

-- Unique index
CREATE UNIQUE INDEX idx_name ON table_name(column_name);

-- With specific algorithm (MySQL)
CREATE INDEX idx_name ON table_name(column_name) USING BTREE;
CREATE INDEX idx_name ON table_name(column_name) USING HASH;

-- Descending order (useful for ORDER BY DESC queries)
CREATE INDEX idx_name ON table_name(column_name DESC);

-- Conditional index (PostgreSQL)
CREATE INDEX idx_name ON table_name(column_name) WHERE condition;

-- Concurrent creation (PostgreSQL - doesn't lock table)
CREATE INDEX CONCURRENTLY idx_name ON table_name(column_name);

-- With included columns (SQL Server, PostgreSQL 11+)
CREATE INDEX idx_name ON table_name(key_column) 
INCLUDE (non_key_column1, non_key_column2);

Dropping Indexes

-- Standard syntax
DROP INDEX index_name ON table_name;  -- MySQL
DROP INDEX index_name;                -- PostgreSQL

-- SQL Server
DROP INDEX table_name.index_name;

-- Check if exists first
DROP INDEX IF EXISTS index_name ON table_name;

Viewing Indexes

-- MySQL
SHOW INDEXES FROM table_name;
SHOW INDEX FROM table_name WHERE Key_name = 'idx_name';

-- PostgreSQL
SELECT * FROM pg_indexes WHERE tablename = 'table_name';

-- SQL Server
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('table_name');

-- Standard SQL (works on most databases)
SELECT * FROM information_schema.statistics 
WHERE table_name = 'table_name';

When to Create Indexes​

success

You SHOULD Create an Index When:

✅ Frequently Used in WHERE Clauses

-- If you run this query 1000 times per day:
SELECT * FROM users WHERE email = 'user@example.com';
-- You NEED this index:
CREATE INDEX idx_users_email ON users(email);

✅ Used in JOIN Conditions

-- Frequently joining these tables:
SELECT o.*, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Create indexes on join columns:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_customers_id ON customers(customer_id);  -- Often already exists as PK

✅ Used in ORDER BY

-- Common sorting pattern:
SELECT * FROM products ORDER BY category, price DESC;
-- Index helps:
CREATE INDEX idx_products_category_price ON products(category, price DESC);

✅ Used in GROUP BY

-- Aggregation queries:
SELECT department, COUNT(*) 
FROM employees 
GROUP BY department;
-- Index helps:
CREATE INDEX idx_employees_department ON employees(department);

✅ Foreign Key Columns

-- Always index foreign keys:
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(id);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

✅ Columns with High Selectivity

-- High selectivity: email, SSN, username (unique or near-unique)
CREATE INDEX idx_users_email ON users(email);

-- NOT low selectivity: gender, boolean flags, status with few values
-- Don't index: gender (only 'M', 'F', 'Other')
danger

You Should NOT Create an Index When:

❌ Table is Small (< 1000 rows)

-- Overhead of index > benefit for tiny tables
-- Database can scan 1000 rows faster than using index

❌ Column Has Low Selectivity

-- Bad: is_active (only TRUE/FALSE values)
-- Bad: gender (only 2-3 values)
-- Bad: status (only 'active', 'inactive', 'pending')

-- Exception: If you're filtering 99% of data
-- Partial index can help:
CREATE INDEX idx_users_inactive ON users(last_login)
WHERE is_active = FALSE;  -- If only 1% are inactive

❌ Column Frequently Updated

-- Think twice before indexing columns that change often
-- Example: 'last_updated', 'view_count', 'login_count'
-- Every UPDATE must update the index too

❌ Already Covered by Composite Index

-- Existing index:
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Redundant: customer_id is already indexed (leftmost column)
CREATE INDEX idx_orders_customer ON orders(customer_id);  -- ❌ Not needed

-- But this would be useful (different leftmost column):
CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);  -- ✓ OK

❌ Table Has Heavy Write Operations

-- Logging tables, temporary staging tables
-- If 90% operations are INSERT, minimize indexes
-- Keep only essential ones

Practical Example​

-- Before indexing
SELECT * FROM employees WHERE department_id = 5;
-- Took 2.8s (full table scan)

-- After indexing
CREATE INDEX idx_department_id ON employees(department_id);
SELECT * FROM employees WHERE department_id = 5;
-- Took 0.03s (index scan)

After indexing department_id, the query optimizer uses an index scan instead of a full table scan — drastically improving performance.

Common Indexing Mistakes​

danger

Mistake #1: Over-Indexing

-- Bad: Index every column "just in case"
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_first_name ON users(first_name);
CREATE INDEX idx_users_last_name ON users(last_name);
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_address ON users(address);
CREATE INDEX idx_users_city ON users(city);
CREATE INDEX idx_users_state ON users(state);
CREATE INDEX idx_users_zip ON users(zip);

-- Problems:
-- ✗ Slows down INSERT/UPDATE/DELETE
-- ✗ Wastes disk space
-- ✗ Database has to choose between many indexes (confusion)

-- Good: Index only frequently queried columns
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_location ON users(state, city);  -- Composite for location queries

Mistake #2: Wrong Column Order in Composite Index

-- Query pattern:
SELECT * FROM orders 
WHERE customer_id = 123 AND order_date >= '2024-01-01';

-- Bad: Date first (less selective)
CREATE INDEX idx_orders_wrong ON orders(order_date, customer_id);

-- Good: Customer first (more selective, used in more queries)
CREATE INDEX idx_orders_right ON orders(customer_id, order_date);

Mistake #3: Indexing Low-Cardinality Columns

-- Bad: Only 2 values (M/F)
CREATE INDEX idx_users_gender ON users(gender);

-- Bad: Only 3-4 values
CREATE INDEX idx_orders_status ON orders(status);

-- Exception: OK if filtering out 99% of data
CREATE INDEX idx_users_suspended 
ON users(last_login, email)
WHERE is_suspended = TRUE;  -- If only 0.1% are suspended

Conclusion​

SQL Indexes are one of the most powerful tools for database performance optimization — when used wisely. They can transform sluggish queries into lightning-fast ones, enabling your applications to scale efficiently and handle millions (or even billions) of rows seamlessly.

However, indexes are a double-edged sword — while they boost read performance, they come with tradeoffs in storage cost, maintenance overhead, and slower write operations. The key is balance: index only what’s necessary based on query patterns, selectivity, and workload characteristics.

Key Takeaways:​

  • Understand your queries first — analyze WHERE, JOIN, ORDER BY, and GROUP BY clauses before creating indexes.

  • Use the right type of index — primary, unique, composite, covering, partial, or full-text — depending on your use case.

  • Monitor and tune continuously — use query planners and performance metrics (EXPLAIN, ANALYZE, SHOW INDEXES) to verify if indexes are being used effectively.

  • Avoid over-indexing — every index adds write overhead. Drop unused or redundant ones regularly.

  • Think strategically — use composite indexes following the left-to-right rule and leverage partial indexes for highly specific queries.

In essence, a well-designed indexing strategy is the foundation of a performant database system. By mastering when and how to use indexes, you’ll unlock the full potential of SQL — delivering faster queries, efficient storage, and a smoother user experience.

🏁 Optimize smartly — not by adding more indexes, but by adding the right ones.

Further Reading​