Advanced MySQL 8

How to organize your columns in an index for good performance

In this section, we will specifically cover the order of the columns in a multi-column index or COMPOUND type. The order in which you will put your columns will allow you to have queries that perform. Now, in what order should we put them? It depends on how you are going to query your table with your queries. An index can be used to perform an exact search or filtering with the WHERE clause.

A specific search is when the values of all columns in the index are specified and the query uses exactly the same order.

We can say that this type of index is defined as follows:

index( col_A, col_B, col_C )

The advantage of a COMPOUND index is beneficial for a query if it uses these columns ...