MySQL Federated Index

1. In a single query, MySQL can use only one index.
2. In a real project, a WHERE clause in an SQL statement typically contains multiple query conditions and is sorted, grouped, and so on.
3. Too many indexes in the table can affect insert and update performance, which simply means that data write performance is affected. Update the index at the same time as the data is being updated.
4. The most practical benefit of course is the fast query speed and good performance.

The use of federated indexes should be noted:

1. MySQL uses a federated index to use only the left portion, such as index (A,B,C), which can be used when the condition is a or a, B or a,b,c, but the index will not be used when the condition is b,c. This is like a book based on the surname, and then the name of the phone book , when looking for a surname, the efficiency will be higher than no conditions, if the first name on the basis of the well-known conditions, the efficiency will be higher, but if only the name of the condition, the phone book will not work .

1. Indexes with higher dispersion should be placed in front of the federated Index, because the high degree of selectivity of the index is high. Consider an extreme situation in which there are 100 records in the data table, and if there are only two cases of a in index (a, A, b), there are 100 cases. This is for querying unique records a = ..., B = ... , we go through all the indexes to see that there are 50 index nodes that meet the a condition, and then we have to walk through the 50 index nodes one after another. If it is index (B,A), first traversing all indexes finds only one index node satisfying the B condition, and then traversing the node discovery also satisfies the a condition. Although the only index node can be found at the end, the second index order greatly improves the efficiency of the Engine traversal index (using the idea of a phone book to think about the problem).
2. View the degree of dispersion of a column: select COUNT (Distinct customer_id), COUNT (distinct staff_id) from payment;

customer_id columns are more discrete and should be indexed (CUSTOMER_ID,STAFF_ID) when building a joint index;

MySQL Federated Index