Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Master database indexing strategies for software engineers. Learn composite index column ordering, the leftmost prefix rule, and covering index design.
Quick answer
A composite index covers multiple columns (colA, colB, colC). According to the leftmost prefix rule, the database can use this index only for queries that filter on colA, (colA AND colB), or (colA AND colB AND colC). It CANNOT use the index for queries filtering only on colB or colC. As a general rule, place equality filters first and range filters last when ordering composite index columns.
FAQs
What is the leftmost prefix rule in database indexes?
A multi-column B-Tree index is sorted like a phone book: first by the first column, then by the second, and so on. You cannot look up someone by first name alone if the book is sorted by (Last Name, First Name). You must supply the leftmost columns to traverse the tree.
Should equality columns or range columns come first in a composite index?
Always place equality columns first (`status = 'active'`) and range columns second (`created_at >= '2026-01-01'`). Once the index evaluates a range filter, it can no longer perform direct index seeks on subsequent columns.
Why shouldn't I just create an index on every column?
Every index adds overhead: write operations (INSERT, UPDATE, DELETE) must update every index on the table, increasing disk I/O, inflating memory buffer usage, and slowing down batch transactions.
What is index selectivity?
Selectivity is the ratio of distinct values to total rows. High-selectivity columns (like user_id or uuid) are excellent index candidates; low-selectivity columns (like is_active boolean) should rarely be indexed alone.
Indexes are the most powerful performance tool available to backend developers. Understanding the leftmost prefix rule and composite column ordering prevents useless indexes and dramatically speeds up database queries.
Key takeaways
Composite index order matters: queries must match the leftmost prefix of the indexed columns.
Consider an index defined as `CREATE INDEX idx_users_org_role ON users (org_id, role);`:
WHERE org_id = 10 AND role = 'admin' -> Uses full index seek.
WHERE org_id = 10 -> Uses leftmost prefix; fast index seek.
WHERE role = 'admin' -> Cannot use the index! (Missing leftmost column org_id).
Correct composite index column ordering
-- Suppose queries frequently filter by tenant and date range:-- SELECT * FROM orders WHERE tenant_id = 42 AND created_at >= '2026-01-01'-- OPTIMAL: Equality first (tenant_id), Range second (created_at)CREATE INDEX idx_orders_tenant_dateON core.orders ( tenant_id ASC, created_at DESC);-- POOR: Range first halts index traversal on tenant_id-- CREATE INDEX idx_orders_date_tenant ON core.orders (created_at, tenant_id);
Covering indexes: Eliminating table lookups
When an index contains every column requested by a query, the database satisfies the request via an 'Index Only Scan' without reading the main table heap:
Creating a covering index with INCLUDE
-- Satisfies the query completely from index memoryCREATE INDEX idx_products_category_coveringON catalog.products (category_id)INCLUDE (product_name, unit_price);-- Query does zero table heap lookups:SELECT product_name, unit_priceFROM catalog.productsWHERE category_id = 5;