Indexes in MySQL

What Is an Index in MySQL?

An index is an ordered access structure that helps MySQL find qualifying rows without reading every table row. Most MySQL indexes such as PRIMARY KEY, UNIQUE and ordinary INDEX use B-trees. An index can support equality, ranges, joins, ordering, grouping and sometimes a covering query.

Indexes trade read efficiency for storage and write-maintenance cost. Good design starts with real query patterns, not a rule to index every WHERE column.

Verified Orders Index Lab

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
) ENGINE = InnoDB;

INSERT INTO orders_index_lab VALUES
(1, 101, 'PAID',      '2026-08-01', 1200.00),
(2, 101, 'PENDING',   '2026-08-05',  500.00),
(3, 102, 'PAID',      '2026-08-03',  750.00),
(4, 103, 'CANCELLED', '2026-08-04',  300.00),
(5, 101, 'PAID',      '2026-08-10', 1500.00),
(6, 102, 'PENDING',   '2026-08-11',  900.00),
(7, 104, 'PAID',      '2026-08-12', 2200.00),
(8, 101, 'PAID',      '2026-08-14',  650.00);

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

The tiny dataset verifies results, not speed. The optimizer may scan all eight rows because that is cheap. Populate representative volume before judging performance.

Primary, Secondary, Unique and Specialized Indexes

Every InnoDB table has a clustered index. If no primary key exists, InnoDB chooses a suitable UNIQUE NOT NULL index or creates a hidden clustered row ID. Define a short, stable primary key deliberately because its columns are carried in secondary index records.

Create, List, Hide and Drop an Index

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

CREATE INDEX idx_order_date
ON orders_index_lab (order_date);

SHOW INDEX FROM orders_index_lab;

Use names that state the leading columns or business access path. CREATE INDEX adds an index to an existing table; primary keys are created through CREATE TABLE or ALTER TABLE.

-- Test optimizer behavior without permanently dropping a secondary index.
ALTER TABLE orders_index_lab
ALTER INDEX idx_order_date INVISIBLE;

ALTER TABLE orders_index_lab
ALTER INDEX idx_order_date VISIBLE;

DROP INDEX idx_order_date ON orders_index_lab;

An invisible secondary index is maintained but ignored by the optimizer unless invisible-index use is explicitly enabled. A primary key cannot be made invisible. Before removing an index, check constraints, workload, replicas and plans that depend on it.

Composite Index and Leftmost-Prefix Rule

For (customer_id, status, order_date), usable lookup prefixes are customer_id; customer_id plus status; and all three columns. The following predicates align with that order:

-- Prefix 1
WHERE customer_id = 101

-- Prefix 2
WHERE customer_id = 101 AND status = 'PAID'

-- Equality columns followed by a date range
WHERE customer_id = 101
  AND status = 'PAID'
  AND order_date >= '2026-08-01'
  AND order_date <  '2026-09-01'

These do not form the same direct lookup prefix:

WHERE status = 'PAID';

WHERE status = 'PAID'
  AND order_date >= '2026-08-01';

MySQL may still use another index, Index Merge, a scan or another optimization. Column order should reflect equality conditions, ranges, sorting, selectivity and the complete workload—not a universal “most selective first” slogan.

Covering query

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

All requested values are in the composite index. InnoDB secondary records also contain primary-key columns, so selecting order_id can often remain covered. Verify the chosen plan instead of assuming coverage.

Write Index-Friendly, Sargable Predicates

A function on a column can prevent use of its ordinary column index for direct lookup. MySQL also supports functional key parts and generated-column indexes for appropriate expressions, but a clear range predicate is often simpler.

• Do not use SELECT * if a narrow result is sufficient.
• Avoid indexing columns that are never filtered, joined, sorted or grouped.
• Low-cardinality status alone may be weak when most rows share one value.
• An index helps less when the query needs most table rows.
• Range conditions can limit how later composite key parts help lookup and ordering.

Verify Plans with EXPLAIN and EXPLAIN ANALYZE

EXPLAIN
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;

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

Index Design and Maintenance Checklist

1. Collect high-cost, high-frequency query patterns.
2. Identify equality, range, join and ordering columns together.
3. Design the fewest composite indexes that serve important patterns.
4. Keep the InnoDB primary key short and stable.
5. Use EXPLAIN before and after; test representative row counts.
6. Check duplicate or redundant indexes before adding another.
7. Measure write latency and storage growth because every relevant index is maintained.
8. Refresh optimizer statistics with ANALYZE TABLE when needed.
9. Use invisible indexes for a reversible optimizer-use test where appropriate.
10. Review indexes as workloads change; yesterday's useful index can become redundant.

Practice: create the composite index; test all three leftmost prefixes; query status alone; compare a YEAR function with a date range; make a secondary index invisible; use EXPLAIN and record the selected plan on a large dataset.

Next lessons explain primary and secondary indexes, composite indexes and EXPLAIN query plans in depth.

Official References

• MySQL 8.4: How MySQL Uses Indexes
• MySQL 8.4: Multiple-Column Indexes
• MySQL 8.4: EXPLAIN Statement

Index structures, leftmost-prefix behavior and plan verification were checked against the official MySQL 8.4 manual.