Composite Indexes

Before (Inefficient Query)

SELECT * FROM orders 
WHERE status = 'shipped' 
AND created_at > '2024-01-01';

After (Optimized Query)

-- Create composite index
CREATE INDEX idx_orders_status_created 
ON orders(status, created_at);

-- Query uses the composite index efficiently
SELECT * FROM orders 
WHERE status = 'shipped' 
AND created_at > '2024-01-01';

Why This Optimization Works

1. Index Column Order Matters: The order of columns in a composite index determines its usefulness. Columns used in equality conditionsi (=status) should come first, followed by columns used in range conditionsi (created_at >).

2. Index Only Scani: With a proper composite index, the database can satisfy the entire query from the index without accessing the table data (index-only scan).

3. Leading Column Rulei: A composite index can only be used if the query filters on the leading (leftmost) column(s).

Composite Index Rules

Good Order (Equality + Range)

CREATE INDEX idx ON table(status, created_at);
-- Used: WHERE status = 'x' AND created_at > 'y'

Bad Order (Range + Equality)

CREATE INDEX idx ON table(created_at, status);
-- NOT used: WHERE status = 'x' AND created_at > 'y'

Column Order in Tables

Sometimes a row may exceed the size of one database page. When this is the case, the database benefits from having indexed columns first since they are the first things read on the page. If a large column comes before the index, it may take the database engine 1, 2, or even 3 page reads to find the index. Place indexed fields before fields with large amounts of data.

-- Recommended: Indexed columns first
CREATE TABLE orders (
    id INT PRIMARY KEY,           -- Indexed, read first
    status VARCHAR(20),           -- Indexed, read second
    notes TEXT,                   -- Large field, read last
    created_at TIMESTAMP          -- Indexed, read third
);

-- Less efficient: Large columns before indexes
CREATE TABLE orders_bad (
    id INT PRIMARY KEY,
    notes TEXT,                   -- Large field read before indexes
    status VARCHAR(20),
    created_at TIMESTAMP
);

Query Coverage

An index-only scani occurs when all required columns are included in the index:

-- Covering index includes all selected columns
CREATE INDEX idx_covering ON orders(status, created_at, order_id, total);
SELECT order_id, total FROM orders WHERE status = 'shipped';