Leftmost Prefix Rule in MySQL: Explanation and Examples

The leftmost prefix rule in MySQL means that when using a multi-column index, MySQL can only use the index if the query filters on the first column or a continuous leftmost set of columns in the index. This rule helps MySQL decide which parts of an index it can use to speed up searches.

How It Works

Imagine you have a multi-column index like a row of lockers labeled from left to right. The leftmost prefix rule says you can only open lockers starting from the first one on the left, and you must open them in order without skipping any. In MySQL, this means if you have an index on columns (A, B, C), the database can use the index if your query filters on A alone, or on A and B together, or on A, B, and C together.

However, if your query filters only on B or on B and C without A, MySQL cannot use this index efficiently because it breaks the left-to-right order. This rule helps MySQL quickly find data by using the most relevant part of the index starting from the left.

Example

This example shows how MySQL uses the leftmost prefix rule with a multi-column index.

sql

CREATE TABLE employees (
  id INT PRIMARY KEY,
  department VARCHAR(50),
  role VARCHAR(50),
  location VARCHAR(50),
  INDEX idx_dept_role_loc (department, role, location)
);

-- Query using leftmost column 'department' only
EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

-- Query using first two columns 'department' and 'role'
EXPLAIN SELECT * FROM employees WHERE department = 'Sales' AND role = 'Manager';

-- Query using columns 'role' and 'location' but skipping 'department'
EXPLAIN SELECT * FROM employees WHERE role = 'Manager' AND location = 'NY';

Output

1. Uses index idx_dept_role_loc (efficient)
2. Uses index idx_dept_role_loc (efficient)
3. Does NOT use idx_dept_role_loc (inefficient, full table scan)

When to Use

Use the leftmost prefix rule to design your indexes and queries for better performance. When you create multi-column indexes, order the columns by how you most often filter your data, starting with the most selective column first.

This rule is useful in real-world cases like filtering employees by department first, then role, then location. If your queries skip the first column in the index, MySQL cannot use the index efficiently, leading to slower searches.

Key Points

• The leftmost prefix rule applies only to multi-column indexes.
• MySQL uses the index starting from the first column and continues left to right without skipping.
• Queries must filter on the first column or a continuous set of leftmost columns to use the index.
• Skipping the first column in the index prevents MySQL from using that index efficiently.

Key Takeaways

MySQL uses multi-column indexes only from the leftmost column in order.

Queries must filter on the first column or a continuous left set of columns to use the index.

Design indexes with the most commonly filtered columns first for best performance.

Skipping the first column in a multi-column index causes MySQL to ignore the index.

Understanding this rule helps write faster queries and better indexes.