How to Write and Optimize SQL Queries: Indexing Basics

Vidensdatabase

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 WHERE clauses
Columns used in JOIN conditions
Columns used in ORDER BY when 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

Browse more articles in Databases.

Vidensdatabase

Kategorier

Kategorier