Master MySQL Indexes: Primary vs. Composite, Equality, Range, and Order Optimization

1. Equality Matching Principle

When a query uses only equality conditions on the leading columns of a composite index (e.g., SELECT * FROM student WHERE name='wx' AND age=1), MySQL can fully utilize the index because the index fields are sorted in the order they appear in the index definition.

2. Leftmost Prefix Matching Principle

MySQL can use a composite index only if the query predicates start with the leftmost column(s) of the index. For an index on (classId, name, age), a condition on classId alone works, as does classId + name, but a condition on age alone cannot use the index because the preceding columns are skipped.

Example: SELECT * FROM student WHERE classId=1 AND name='wx' uses the index, while SELECT * FROM student WHERE age=1 results in a full table scan.

3. Range Scan Rules

Range conditions (e.g., classId > 1 AND classId < 4) can use the index only on the leftmost column. Adding a range on a later column (e.g., name > 'a' AND name < 'x') does not help if the preceding column is already a range, because the index order for the later column becomes undefined.

4. Equality + Range Combination

If the leftmost column is matched with equality and a subsequent column uses a range, the index can still be used. For example,

SELECT * FROM student WHERE classId=1 AND name > 'a' AND name < 'x'

first locates the rows with classId=1 (using the index) and then applies the range on name within that subset.

5. ORDER BY + LIMIT Optimization

When the ORDER BY clause follows the exact column order of a composite index (all ascending or all descending), MySQL can retrieve the sorted rows directly from the index without an additional sort step. Example:

SELECT name, age, address FROM student ORDER BY name, age, address LIMIT 10

uses the (name, age, address) index for both ordering and pagination.

6. GROUP BY Optimization

Grouping on a column that has an index allows MySQL to read rows in index order, eliminating the need for a temporary table and extra sorting. For SELECT COUNT(*) FROM student GROUP BY name, creating an index on name lets MySQL stream the grouped results directly.

7. Summary

The core principles for effective MySQL query optimization are:

Understand how primary and composite indexes are stored as B+ trees.

Apply the equality‑matching principle on leading index columns.

Respect the leftmost‑prefix rule for both equality and range predicates.

Combine equality on the first column with a range on the next column when possible.

Align ORDER BY and GROUP BY clauses with existing indexes to avoid extra sorting or temporary tables.

Tailor index design to actual query patterns; there is no one‑size‑fits‑all solution.