Composite Index #

-- Composite index: (A, B, C)
CREATE INDEX idx_example ON orders (user_id, status, created_at);

-- ✓ Can use the index — starting from the leftmost column (user_id)
WHERE user_id = 1
WHERE user_id = 1 AND status = 'active'
WHERE user_id = 1 AND status = 'active' AND created_at >= '2026-01-01'

-- ✗ CANNOT use the index optimally
WHERE status = 'active'                         -- skips user_id
WHERE created_at >= '2026-01-01'                -- skips user_id and status
WHERE status = 'active' AND created_at >= '2026-01-01'  -- skips user_id

-- Index: (user_id, status, created_at)

-- Can use the index for user_id, then filter created_at outside the index
WHERE user_id = 1 AND created_at >= '2026-01-01'
-- → Uses the index for user_id=1, then scans all user_id=1 rows
--   and filters created_at at the engine level (not optimal but uses the index)
-- → Extra: "Using index condition"

-- Vs. a query using all prefix columns:
WHERE user_id = 1 AND status = 'active' AND created_at >= '2026-01-01'
-- → Far more efficient: straight to the exact point in the B-Tree

-- Create a table and index for demonstration
CREATE TABLE orders (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    BIGINT UNSIGNED NOT NULL,
    status     VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total      DECIMAL(15,2) NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_orders_composite (user_id, status, created_at)
);

-- Query 1: uses all prefixes → very efficient
EXPLAIN SELECT id, total
FROM orders
WHERE user_id = 42 AND status = 'paid' AND created_at >= '2026-01-01';
-- type: range | key: idx_orders_composite | rows: ~150 | Extra: Using index condition

-- Query 2: skips the middle column → still uses the index but less efficiently
EXPLAIN SELECT id, total
FROM orders
WHERE user_id = 42 AND created_at >= '2026-01-01';
-- type: ref | key: idx_orders_composite | rows: ~8400 | Extra: Using index condition

-- Query 3: starts from the middle column → index unused
EXPLAIN SELECT id, total
FROM orders
WHERE status = 'paid';
-- type: ALL | key: NULL | rows: 2,500,000 | Extra: Using where

-- The query to optimize:
SELECT * FROM orders
WHERE user_id = 42
  AND status = 'paid'
  AND created_at >= '2026-01-01';

-- ANTI-PATTERN: range column first
CREATE INDEX idx_wrong ON orders (created_at, user_id, status);
-- → The index can use created_at for the range, but user_id and status
--   can't be used after a range — the database is forced to scan all
--   rows in the created_at range, then filter user_id and status

-- CORRECT: equality columns first, range column last
CREATE INDEX idx_correct ON orders (user_id, status, created_at);
-- → The database goes straight to user_id=42, status='paid',
--   then scans the created_at range from that point
-- → Far fewer rows read

-- Table products: 1 million rows
-- category_id: 50 categories (low selectivity, ~20,000 rows per category)
-- brand_id: 5,000 brands (high selectivity, ~200 rows per brand)

-- ANTI-PATTERN: category_id (low selectivity) first
CREATE INDEX idx_products_wrong ON products (category_id, brand_id, status);
-- WHERE category_id = 5 AND brand_id = 1234 AND status = 'active'
-- → Starts from 20,000 rows (category_id=5), filters to brand, filters to status

-- CORRECT: brand_id (high selectivity) first
CREATE INDEX idx_products_correct ON products (brand_id, category_id, status);
-- WHERE category_id = 5 AND brand_id = 1234 AND status = 'active'
-- → Starts from ~200 rows (brand_id=1234), filters to category, filters to status
-- → Far fewer rows processed from the start

-- Note: the order in WHERE doesn't have to match the index order
-- The query planner adjusts automatically

-- Query: the latest published articles from a specific author
SELECT id, title, published_at
FROM articles
WHERE author_id = 7 AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

-- ANTI-PATTERN: an index only for WHERE, ORDER BY not covered
CREATE INDEX idx_articles_filter ON articles (author_id, status);
-- → The database can filter with the index, but sorting published_at needs filesort
-- Extra: "Using index condition; Using filesort"

-- CORRECT: add the ORDER BY column to the index
CREATE INDEX idx_articles_full ON articles (author_id, status, published_at);
-- → The database scans the index from (author_id=7, status='published')
--   and the rows are already in published_at order
-- → No filesort
-- Extra: "Using index condition" (without filesort)

-- For DESC: in MySQL 8.0+ you can specify the sort direction per column
CREATE INDEX idx_articles_desc ON articles (author_id, status, published_at DESC);
-- → ORDER BY published_at DESC now without filesort and without a backward scan

-- The query to optimize
SELECT id, title, status, published_at
FROM articles
WHERE author_id = 7 AND status = 'published'
ORDER BY published_at DESC
LIMIT 20;

-- An index covering only the WHERE and ORDER BY columns
CREATE INDEX idx_articles_where_order ON articles (author_id, status, published_at);

-- The database still needs to read the table for the 'title' and 'id' columns
-- Extra: "Using index condition" (still a table lookup)

-- Covering index: add all SELECTed columns
CREATE INDEX idx_articles_covering ON articles (author_id, status, published_at, title, id);
-- Now all needed columns are in the index
-- Extra: "Using index" ← this is the sign a covering index is active
-- No reads to the main table at all

-- Query: a specific user's order list, filtered by status, sorted newest, paginated
SELECT id, product_id, total, status, created_at
FROM orders
WHERE user_id = 42
  AND status IN ('pending', 'paid')
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Analysis:
-- → user_id: equality, high selectivity → position 1
-- → status: equality (IN = multiple equality) → position 2
-- → created_at: ORDER BY → position 3 (no range in WHERE remaining)
-- → id, product_id, total: SELECT columns → add for covering

-- Optimal index:
CREATE INDEX idx_orders_user_status_created
    ON orders (user_id, status, created_at DESC, id, product_id, total);

-- Verification:
EXPLAIN SELECT id, product_id, total, status, created_at
FROM orders
WHERE user_id = 42 AND status IN ('pending', 'paid')
ORDER BY created_at DESC LIMIT 20;
-- → type: range, key: idx_orders_user_status_created
-- → Extra: Using index (covering index active, no filesort)

-- Query: tenant transaction report within a time range, filtered by status
SELECT id, amount, type, created_at
FROM transactions
WHERE tenant_id = 'tenant-abc'
  AND status = 'completed'
  AND created_at BETWEEN '2026-01-01' AND '2026-01-31';

-- Analysis:
-- → tenant_id: equality → position 1
-- → status: equality → position 2
-- → created_at: BETWEEN (range) → position 3, must come after equality

-- ANTI-PATTERN: created_at first
-- CREATE INDEX idx_wrong ON transactions (created_at, tenant_id, status);
-- → Scans the entire January range (all tenants), then filters tenant and status

-- CORRECT:
CREATE INDEX idx_transactions_tenant_status_date
    ON transactions (tenant_id, status, created_at);
-- → Straight to tenant_id='tenant-abc' + status='completed'
-- → Scan the created_at range from that point
-- → Only reads the relevant transactions

-- Query: active products by category, sorted by name
SELECT id, name, price, stock
FROM products
WHERE category_id = 15
  AND deleted_at IS NULL
ORDER BY name ASC;

-- The deleted_at IS NULL condition is almost always true
-- (most rows aren't deleted) → very low selectivity
-- Don't put it first

-- The right index:
CREATE INDEX idx_products_category_deleted_name
    ON products (category_id, deleted_at, name);
-- → category_id first (higher selectivity)
-- → deleted_at: even though selectivity is low, it must be there so name
--   can be leveraged for sorting without filesort
-- → name: for ORDER BY name ASC

-- Note: IS NULL can be used in B-Tree indexes in MySQL and PostgreSQL
-- NULL values are stored and can be indexed

-- Query: unread notifications for a specific user, not yet expired
SELECT id, title, type, created_at
FROM notifications
WHERE user_id = 99
  AND is_read = FALSE
  AND (expires_at IS NULL OR expires_at > NOW());

-- The expires_at column has a complex OR condition
-- For cases like this, a simple composite index already helps:
CREATE INDEX idx_notifications_user_read_created
    ON notifications (user_id, is_read, created_at DESC);
-- → The database goes straight to user_id=99, is_read=FALSE
-- → expires_at filtered at the engine after the index lookup (Using index condition)
-- → No full scan — the processed rows are already very few

-- Table: orders (5 million rows)
-- Dominant query: WHERE user_id = ? AND status = ?

-- Approach A: two single-column indexes
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status  ON orders (status);

-- What happens when the query runs:
-- MySQL can merge two indexes (index merge), but this is rarely optimal:
-- 1. Read all user_id=42 rows from idx_orders_user_id → 8,000 rows
-- 2. Read all status='paid' rows from idx_orders_status → 1,200,000 rows
-- 3. Intersect both sets → an expensive merge operation
-- Extra: "Using intersect(idx_orders_user_id, idx_orders_status); Using where"

-- Approach B: one composite index
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
-- → Straight to user_id=42 AND status='paid' → maybe 400 rows
-- → No merge, no intersect
-- Extra: "Using index condition"

-- Performance comparison:
-- Approach A: reads 8,000 + 1,200,000 rows, merges → slow
-- Approach B: reads ~400 rows directly → fast

-- ✗ Anti-pattern 1: reversed column order (range before equality)
CREATE INDEX idx_wrong ON orders (created_at, user_id, status);
-- WHERE user_id = 42 AND status = 'paid' AND created_at >= '2026-01-01'
-- → The database starts from the created_at range, then filters user_id and status
-- ✓ Solution: (user_id, status, created_at)

-- ✗ Anti-pattern 2: composite indexes redundant with their prefixes
CREATE INDEX idx_a ON orders (user_id);
CREATE INDEX idx_b ON orders (user_id, status);          -- idx_a already covered
CREATE INDEX idx_c ON orders (user_id, status, created_at);  -- idx_b already covered
-- Three indexes, but idx_a and idx_b are never used because idx_c covers everything
-- Write overhead for three indexes, benefit from only one
-- ✓ Solution: drop idx_a and idx_b, keep only idx_c

-- ✗ Anti-pattern 3: too many columns in one composite index
CREATE INDEX idx_too_wide ON products
    (category_id, brand_id, status, price, name, description, stock, weight);
-- → A very large index, slow on writes
-- → The description (TEXT) column in the index is ineffective
-- ✓ Solution: only include columns actually used in queries

-- ✗ Anti-pattern 4: one composite index for queries with different patterns
-- Query A: WHERE user_id = ? AND status = ?
-- Query B: WHERE status = ? AND created_at >= ?
-- One index can't be optimal for both
-- CREATE INDEX idx_compromise ON orders (user_id, status, created_at);
-- → Query B still can't use the index because it starts from status
-- ✓ Solution: create two separate indexes matching each query's pattern

-- ✗ Anti-pattern 5: not checking EXPLAIN after creating a composite index
-- An index created but never verified whether it's actually used
-- ✓ Solution: always run EXPLAIN after creating a new index