Database Indexing Strategies Cheat Sheet

Index types, composite column ordering, query plan reading, and common anti-patterns for tuning read performance across relational databases.

2 PagesAdvancedMar 20, 2026

Creating Indexes

Common index-creation patterns.

sql

-- Single-column indexCREATE INDEX idx_orders_customer ON orders(customer_id);-- Composite index — column order mattersCREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);-- Unique indexCREATE UNIQUE INDEX idx_users_email ON users(email);-- Partial/filtered index (Postgres/SQL Server)CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Reading a Query Plan

What to look for in EXPLAIN output.

sql

EXPLAIN ANALYZESELECT * FROM ordersWHERE customer_id = 42 AND order_date > '2024-01-01';-- Look for:--   Index Scan / Index Only Scan   (good, index is used)--   Seq Scan on a large table      (possible missing index)--   Bitmap Heap Scan               (multiple indexes combined)

Index Types

The main index structures relational databases offer.

B-tree- default index type; good for equality, range queries, and sorting
Hash- fast equality lookups only, no range query support
Composite (compound)- an index on multiple columns; the leftmost-prefix rule applies
Covering index- includes every column a query needs, enabling index-only scans
Partial/filtered- indexes only rows matching a WHERE condition; smaller and faster
GIN / GiST- Postgres index types for full-text search, arrays, JSONB, and geospatial data

Trade-offs & Anti-Patterns

Where indexing helps and where it hurts.

Write overhead- every INSERT/UPDATE/DELETE must also update each affected index
Over-indexing- too many indexes slow writes and bloat storage without helping reads
Low-cardinality columns- indexing a boolean or similar column rarely helps query performance
Leftmost-prefix rule- an index on (a, b, c) helps queries on (a), (a,b), (a,b,c), not (b) alone
Leading wildcard- LIKE '%term' can't use a standard B-tree index; LIKE 'term%' can
Function on indexed column- WHERE LOWER(email) = 'x' skips a plain index unless a matching expression index exists

Pro Tip

Order composite index columns by equality-filtered columns first, then range-filtered columns, then columns used only for sorting — this lets the query planner use as much of the index as possible.

Was this cheat sheet helpful?

Database Normalization Cheat Sheet

ACID Properties Cheat Sheet

Explore Topics

#DatabaseIndexingStrategies#DatabaseIndexingStrategiesCheatSheet#Database#Advanced#CreatingIndexes#ReadingAQueryPlan#IndexTypes#Trade#Databases#CheatSheet#SkillVeris

Sri Hayavadhana Info-Tech

Professional Web Designing Services