Composite Index in MySQL

Composite Index Meaning and Verified Lab

A composite index, also called a multiple-column index, stores two or more columns as one ordered key. An index on (customer_id, status, order_date) is sorted first by customer_id, then by status within each customer, then by order_date within each customer-status group.

DROP TABLE IF EXISTS orders_index_lab;
CREATE TABLE orders_index_lab (
  order_id INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  order_date DATE NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  notes VARCHAR(100)
) ENGINE = InnoDB;

INSERT INTO orders_index_lab VALUES
(1,101,'PAID',     '2026-08-01',1200.00,'School books'),
(2,101,'PENDING',  '2026-08-05', 500.00,'Stationery'),
(3,102,'PAID',     '2026-08-03', 750.00,'Uniform'),
(4,103,'CANCELLED','2026-08-04', 300.00,'Art kit'),
(5,101,'PAID',     '2026-08-10',1500.00,'Lab equipment'),
(6,102,'PENDING',  '2026-08-11', 900.00,'Sports kit'),
(7,104,'PAID',     '2026-08-12',2200.00,'Computer accessory'),
(8,101,'PAID',     '2026-08-14', 650.00,'Notebooks');

CREATE INDEX idx_customer_status_date
ON orders_index_lab (customer_id, status, order_date);

The eight rows make output verifiable. They are too few for performance conclusions; use representative volume and distribution to compare plans.

The Leftmost-Prefix Rule

For (customer_id, status, order_date), the natural lookup prefixes are:

1. (customer_id)
2. (customer_id, status)
3. (customer_id, status, order_date)

“No” does not mean MySQL is forbidden to touch the index. It may choose a full index scan, skip scan where applicable, Index Merge with other indexes, or another plan. The precise rule is that the condition does not form a leftmost prefix for ordinary direct lookup.

How to Choose Composite-Index Column Order

Begin with an important query pattern, not isolated columns:

SELECT order_id, order_date, total_amount
FROM orders_index_lab
WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'
ORDER BY order_date;

The index order places equality conditions customer_id and status first, followed by the range and ordering column order_date. This is a strong candidate for that pattern. Still check the complete workload:

• Which predicates are equality, IN lists or ranges?
• Which columns join tables?
• Does ORDER BY or GROUP BY follow compatible key order and direction?
• How selective is the combined prefix on real data?
• Which columns must be returned, and is coverage worth extra width?
• Which existing indexes would become redundant?

Equality, Range and the First Gap

With equality on leading parts, MySQL can navigate to a narrow contiguous section. A range on the next key part can bound that section:

WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'

A missing leading key part creates a gap. Later conditions can still filter rows, but they do not repair the missing direct-lookup prefix. A range also commonly limits how later key parts narrow the index interval:

CREATE INDEX idx_customer_date_status
ON orders_index_lab (customer_id, order_date, status);

-- customer_id is equality; order_date is range.
-- status follows the range and may filter, but usually does not narrow
-- the same B-tree interval as an equality key part before the range.
WHERE customer_id = 101
  AND order_date >= '2026-08-01'
  AND status = 'PAID';

This is why key order should mirror important predicate shapes. Do not memorize a simplistic sentence; inspect used_key_parts in JSON/TREE output and actual rows in EXPLAIN ANALYZE.

ORDER BY, Direction and Covering Indexes

SELECT order_id, status, order_date
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

Because the first two key parts are fixed and the next part is order_date, the index can be a good candidate to produce rows in order without a separate sort. Whether it does so depends on the full plan. Mixed directions and joins can change eligibility; MySQL supports descending key parts when their direction matches the query pattern.

CREATE INDEX idx_customer_status_date_amount
ON orders_index_lab
  (customer_id, status, order_date, total_amount);

SELECT order_id, order_date, total_amount
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

The wider index contains every requested secondary value, and InnoDB stores order_id as the primary key in secondary records. It can therefore be covering. Coverage reduces clustered row lookups, but extra key parts consume storage and increase write maintenance. Do not turn every query into a giant covering index.

Composite Index vs Separate Indexes

-- Separate indexes
CREATE INDEX idx_customer ON orders_index_lab (customer_id);
CREATE INDEX idx_status   ON orders_index_lab (status);

-- Workload-aligned composite alternative
CREATE INDEX idx_customer_status
ON orders_index_lab (customer_id, status);

For customer_id = ? AND status = ?, a composite index can fetch the combined key range directly. With separate indexes, the optimizer may choose one index and filter the other predicate, or use Index Merge. Composite is often better for the paired pattern, but separate idx_status may still be necessary for status-only reports.

An index on (customer_id, status) generally makes a separate (customer_id) index redundant for simple lookup because customer_id is its leftmost prefix. Exceptions can arise from index width, uniqueness, constraints and workload details. Use invisible-index testing or a controlled drop plan instead of guessing.

ALTER TABLE orders_index_lab
ALTER INDEX idx_customer INVISIBLE;

-- Recheck important plans and production metrics.

ALTER TABLE orders_index_lab
ALTER INDEX idx_customer VISIBLE;

Verify Key Parts with EXPLAIN

SHOW INDEX FROM orders_index_lab;

EXPLAIN
SELECT order_id, order_date, total_amount
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID'
ORDER BY order_date;

EXPLAIN FORMAT=JSON
SELECT order_id, order_date
FROM orders_index_lab
WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01';

EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id, order_date
FROM orders_index_lab
WHERE customer_id = 101 AND status = 'PAID';

Composite-Index Design Checklist

1. Write down high-frequency, high-cost query shapes.
2. Group equality, join, range and order requirements per query.
3. Choose the narrowest key order that serves several important patterns.
4. Apply the leftmost-prefix rule and identify every gap.
5. Decide whether ORDER BY can follow the index after leading equalities.
6. Add output columns for coverage only when measured benefit justifies width.
7. Check duplicate and prefix-redundant indexes.
8. Measure read improvement plus INSERT/UPDATE/DELETE and storage cost.
9. Compare estimates with actual rows on representative data.
10. Review again when workload or data distribution changes.

Continue with primary and secondary indexes, EXPLAIN plans and query optimization.

Official References

• MySQL 8.4: Multiple-Column Indexes
• MySQL 8.4: ORDER BY Optimization
• MySQL 8.4: EXPLAIN Output Format

Leftmost-prefix behavior, plan fields, covering signals and ordering trade-offs were checked against the official MySQL 8.4 manual.