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
Building a Covering Index
Adding non-key columns with INCLUDE so the query never touches the heap.
sql
-- Postgres: put filter columns in the key, output-only columns in INCLUDECREATE INDEX idx_orders_covering ON orders (customer_id, order_date) INCLUDE (status, total);-- Now this query is answered entirely from the index (Index Only Scan)SELECT status, total FROM ordersWHERE customer_id = 42 AND order_date > '2024-01-01';
Expression / Functional Indexes
Indexing the output of an expression so filters using that expression can still use the index.
sql
-- Without this, WHERE LOWER(email) = ... does a sequential scanCREATE INDEX idx_users_lower_email ON users (LOWER(email));SELECT * FROM users WHERE LOWER(email) = '[email protected]';-- JSONB path expression index (Postgres)CREATE INDEX idx_events_payload_type ON events ((payload->>'type'));
Index Bloat & Maintenance
Finding unused or bloated indexes and rebuilding them without downtime.
sql
-- Postgres: indexes that have never been scanned (candidates for removal)SELECT relname AS table, indexrelname AS index, idx_scanFROM pg_stat_user_indexesWHERE idx_scan = 0ORDER BY relname;-- Rebuild an index without locking writers (Postgres)REINDEX INDEX CONCURRENTLY idx_orders_customer_date;-- MySQL/InnoDB: force a rebuild to reclaim fragmented pagesALTER TABLE orders ENGINE=InnoDB;
Clustered Index Layout (InnoDB)
How the primary key shapes physical row storage and why secondary indexes cost an extra lookup.
sql
-- InnoDB stores the table itself as a B-tree keyed on the primary key-- (the "clustered index") — rows are physically ordered by PK.CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, -- clustered index customer_id INT, order_date DATE) ENGINE=InnoDB;-- A secondary index only stores the PK value, not the full row,-- so a lookup via it does: secondary index -> PK -> clustered index ("bookmark lookup")CREATE INDEX idx_customer ON orders(customer_id);
Advanced Indexing Concepts
Terms that come up once you move past single-table B-tree basics.
- Index-only scan- the planner satisfies a query entirely from the index without touching the table heap
- Bookmark lookup / RID lookup- the extra hop from a secondary index entry back to the full row when the index doesn't cover the query
- Index selectivity- ratio of distinct values to total rows; high selectivity makes a column a good index candidate
- Index intersection- the planner combines two separate single-column indexes (e.g. via bitmap AND) instead of using a composite one
- Fill factor- percentage of each index page left empty to reduce page splits from future inserts/updates
- Write amplification- each additional index multiplies the I/O cost of every INSERT/UPDATE/DELETE on that table
- Fragmentation- page splits and deletes leave gaps over time, degrading range-scan performance until a rebuild
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?
Explore Topics
#DatabaseIndexingStrategies#DatabaseIndexingStrategiesCheatSheet#Database#Advanced#CreatingIndexes#ReadingAQueryPlan#IndexTypes#Trade#Databases#CheatSheet#SkillVeris
