Sequential test scheme for federated indexes under MySQL b-tree index

Column order to be aware of using federated indexes
For example, when using
SELECT * from user where x=1 and y=2;
The index that should be created may be the add key (x, y)
How to determine the order of indexes
General Experience
can use
Select COUNT (Distinct x)/count (x) as X_selectivity,
COUNT (distinct y)/count (y) as x_selectivity,
Count (*),
from user;
row1***************************
x_selectivity:0.0001
y_selectivity:0.0312
COUNT (*): 16022

The higher the selectivity in X, the more you can put in the first column
Alert table User add key (x, y);

In addition, similar optimizations can be found in historical slow queries.

Like what
SELECT * from user where x=1 and y=2;

Select SUM (x=1), sum (y=2) from User\g;
row1**********************************
SUM (x=1): 7992
SUM (y=2): 30

The selectivity of Y is higher and can be placed in the first column

And look at the selectivity of the X column for y=2.
Select SUM (x=1) from user where y=2
row1**********************************
SUM (x=1): 17

However, there is a possibility that MySQL query is unfair, the overall performance of the server may be worse, so we need to extract the worst query to do this query work

This kind of work is called Sarg by some optimization geek geek. This is the abbreviation for "searchable parameters (searchable argument)".

Sequential test scheme for federated indexes under MySQL b-tree index