• Scenario: How Would You Index This Query?
• Understanding Multi-Filed Indexing
• How Does Index Order Affect Range Query?
  • Case 1: Using INDEX(a, b, c)
  • Case 2: Using INDEX(b, c, a)
• Tips: How to Choose the Right Index Order?

Scenario: How Would You Index This Query?

Image you have a table, and you need to optimize the following query:

SELECT * FROM my_table WHERE a >= 1 AND b = 1 AND c = 1;

At first glance, you might think adding a multi-field index like INDEX(a, b, c) will solve the problem. But is that really the best choice?

Understanding Multi-Filed Indexing

A multi-field index (also called a composite index) is an index that covers more than one column. For example:

CREATE INDEX index_abc ON my_table (a, b, c);

This means the table is sorted in lexicographical order by:

1. Column a first.
2. Then by b within rows where a is the same.
3. Finally by c within rows where both a and b are the same.

But how does the order of column in an index affect performance in range query like a >= 1 ?

How Does Index Order Affect Range Query?

Let’s compare two different multi-field indexes:

INDEX(a, b, c)
INDEX(b, c, a)

And we will analyze their performance on this query:

SELECT * FROM my_table WHERE a >= 1 AND b = 1 AND c = 1;

Case 1: Using INDEX(a, b, c)

With INDEX(a, b, c), the query looks like this:

SELECT * FROM my_table WHERE a >= 1 AND b = 1 AND c = 1;

Here’s how MySQL processes it:

1. The database first finds rows where a >= 1;
2. However, because a is a range condition, the database cannot use the remaining columns (b and c) efficiently. The sorting order for b and c is broken after the range condition on a.
3. As a result, the database has to perform a full scan within the a >= 1 range to find rows that match b = 1 and c = 1.

Case 2: Using INDEX(b, c, a)

Now let’s change the index to INDEX(b, c, a) and adjust the query accordingly:

SELECT * FROM my_table WHERE b = 1 AND c = 1 AND a >= 1;

Here’s how MySQL processes it:

1. First database will find rows where b = 1.
2. Within that range, it finds rows where c = 1.
3. Finally, it applies the range condition on a.

Because b and c are exact mathces, the database could efficiently use the index for both columns before applying the range condition on a.

Tips: How to Choose the Right Index Order?

1. Put equality conditions first: Columns that use = should come first in the index.
2. Put range conditions last: Columns that use >=, >, <, etc., should be placed last in the index.
3. Design indexes based on your most frequent queries.