When does MySQL use a single-column index? What is used with a federated index?

I have a table students table, there are 3 fields, id,name,age I want to query through name and age, in which two fields are created federated index? Or do you create a single-column index on nage and age, respectively? Multiple field queries when do I use a federated index to create a single-column index separately?

Fan Xiaopeng
Links: https://www.zhihu.com/question/40736083/answer/88191544
Source: Know
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source.

1, first to determine the goal of optimization, in what kind of business scenario, the size of the table and so on. If the table is small, you may not need to index it.
2, which fields can be indexed, usually where, order by, or field after group by.
3, the record changes need to maintain the index, so there will be overhead, to measure the index after the build and loss.

Student table, you can think of the name of the repetition of a smaller, and age of repetition is relatively large, for a single-column index, it is more appropriate to build on a low-stress column.

For SELECT * from students where Name= ' Zhang San ' and age=18; Two things that the Lord says
A. Name and age are independently indexed.
In general, MySQL chooses one of the indexes, and name is more likely because MYSQ will count the repetitions on each index and choose a field with a low repetition level. Another age index is not used, but there is also the overhead of maintaining the index, so the age index does not need to be created.

B. Federated index of name and age
This index is the best fit, and MySQL will use this index directly. However, the cost of maintenance is larger relative to the individual name index, and the index data occupies a larger amount of storage space.

Back to see, is it necessary to use a federated index? My view is not necessary, because the school may have the name of the person, but relatively few. With the name can be more accurate to find records, even if there is less duplication.

Under what circumstances is it better to use a federated index? For example, a university elective teacher needs to create a relational table with 2 fields, student_id and teacher_id, and wants to find out whether a teacher or a student has a relationship.
A student union choose dozens of teachers, a teacher will take hundreds of students
If the index is only for student_id, the index will select dozens of records, then in memory where a bit, remove the rest of the teacher.
Conversely, if you index only teacher_id, the index will pick up hundreds of records, then in memory where, remove the rest of the students.
Neither case is optimal, and this is the best time to use a federated index to find the corresponding record directly from the index. To create an index instance:

First create a table: CREATE TABLE students (ID int primary key,name varchar (), age int);

Syntax for creating a single index: CREATE index index name on table name (field name)

The index name is usually: Table name _ Field name

Index creation: CREATE INDEX students _id on students (ID);

Syntax for creating a federated Index: CREATE index index name on table name (field name 1, field name 2)

Creating a federated index for name and age: Create INDEX students _name_age on students (name,age)

Mysql_mysql Joint Index details and considerations

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.

CREATE TABLE Test (
a int,
b int,
c int,
KEY A (A,B,C)
);

Excellent: SELECT * from Test where a=10 and b>50
Poor: SELECT * from Test where A50

Excellent: SELECT * FROM Test order by a
Poor: SELECT * from Test order by B
Poor: SELECT * from Test ORDER by C

Excellent: SELECT * from Test where a=10 order by a
Excellent: SELECT * from Test where a=10 order by B
Poor: SELECT * from Test where a=10 order by C

Excellent: SELECT * from Test where a>10 order by a
Poor: SELECT * from Test where a>10 order by B
Poor: SELECT * from Test where a>10 order by C

Excellent: SELECT * from Test where a=10 and b=10 order by a
Excellent: SELECT * from Test where a=10 and b=10 order by B
Excellent: SELECT * from Test where a=10 and b=10 order by C

Excellent: SELECT * from Test where a=10 and b=10 order by a
Excellent: SELECT * from Test where a=10 and b>10 order by B
Poor: SELECT * from Test where a=10 and b>10 order by C

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.

When does MySQL use a single-column index? What is used with a federated index?