MySQL Federated Index Detailed

MySQL Federated Index Detailed

A federated index is also called a composite index. For composite indexes: MySQL left-to-right uses fields from the index, and a query can use only one part of the index, but only the leftmost section. For example, the index is key index (A,B,C). can support a | a,b| A,b,c 3 combinations to find, but B,c is not supported. The index is very effective when the leftmost field is a constant reference.

An index on two or more columns is called a composite index.
With additional columns in the index, you can narrow the scope of your search, but using an index with two columns differs from using two separate indexes. The structure of a composite index is similar to a phone book, where a person's name consists of a surname and a name, and the phone book is first sorted by last name, and then by name for people with the same last name. If you know the last name, the phone book will be useful, and if you know the first and last names, the phone book is more useful, but if you only know the first name, the phone book will be useless.
So when you create a composite index, you should carefully consider the order of the columns. A composite index is useful when you perform a search on all columns in an index or when you perform a search on only the first few columns, and the composite index is useless when you perform a search on any subsequent column.
such as: the establishment of the name, age, gender composite index.

Indexing principles

1. The fewer indexes the better
Cause: When modifying the data, the first index is updated to reduce the write speed.
2. The narrowest field is placed on the left side of the key
3. Avoid file sort sorting, temporary tables, and table scans.

MySQL Federated Index Detailed