Proper indexing is often the single highest-impact database performance optimization available — this guide covers the fundamentals of when and how to index effectively.
What an Index Actually Does
An index creates a separate, ordered data structure allowing the database to quickly locate rows matching a query condition, without scanning every row in the table — similar in concept to a book's index letting you find a topic without reading every page.
The Cost of Indexes (Not Free)
Indexes speed up reads but add overhead to writes (every INSERT/UPDATE/DELETE must also update relevant indexes) and consume additional storage — indexing everything indiscriminately isn't the answer; index deliberately based on actual query patterns.
When to Add an Index
- Columns frequently used in
WHEREclauses - Columns used in
JOINconditions - Columns used in
ORDER BYwhen sorting large result sets - Foreign key columns (often not automatically indexed, depending on the database)
Creating a Basic Index
CREATE INDEX idx_users_email ON users(email);Composite (Multi-Column) Indexes
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);Useful when queries frequently filter on multiple columns together — column order matters: put the most selective/frequently-filtered column first for best effectiveness.
Identifying Missing Indexes: Using EXPLAIN
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;Shows the query execution plan — look for "Seq Scan" (PostgreSQL) or "Full Table Scan" (MySQL) on large tables where you'd expect an index to be used instead, indicating a missing or unused index.
Checking Existing Indexes
-- PostgreSQL
\d table_name
-- MySQL/MariaDB
SHOW INDEX FROM table_name;Finding Unused Indexes (Wasted Overhead)
-- PostgreSQL
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;Indexes that are never actually used still incur write overhead without any read benefit — periodically review and consider removing genuinely unused indexes.
Common Indexing Mistakes
- Indexing every column "just in case," adding unnecessary write overhead
- Missing indexes on foreign key columns used in frequent JOINs
- Composite index column order not matching actual query filter patterns
- Not indexing columns used in ORDER BY for frequently-sorted large result sets
Partial Indexes (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;Indexes only a subset of rows matching a condition — smaller and more efficient than a full-table index when queries consistently filter for that specific condition.
Understanding Query Cost Beyond Just Indexing
Indexing addresses lookup efficiency, but query structure itself matters too — avoid unnecessary SELECT * when only specific columns are needed, and be mindful of how JOINs and subqueries affect overall query cost.
Testing Index Impact
EXPLAIN ANALYZE SELECT ...;Compare execution time and plan before and after adding an index, confirming it's actually being used and providing the expected improvement, rather than assuming based on theory alone.
Common Errors
Added an index but query is still slow — verify with EXPLAIN that the index is actually being used; sometimes the query planner chooses not to use an index due to low table selectivity or outdated statistics (ANALYZE the table to refresh statistics).
Continue Reading
- How to Monitor Database Performance and Slow Queries
- How to Optimize PostgreSQL Performance for a VPS
- How to Tune MySQL/MariaDB Performance for a VPS
Browse more articles in Databases.
