MySQL optimization--where The order of conditional fields affects efficiency (02)

Student Table Student

ID (number)	Name (first name)	Age (Ages)	Height (tall)
1	Tommy	26	170
2	Jerry	23	180
3	Frank	30	160

As the table shows, there are only 3 data presented here, and here we assume there are 10,000 data,

Check all students aged 25 and above and above 170

Select * from Student where age > and height >;//Normally this can be written,

Hypothesis 1: There are 8000 students over 25 years of age, and more than 170 of the height of a student,

The order of execution of the above SQL and the number of rows queried should be:

1. First check the age of 25 years old students, the result is 8,000 records,

2. Again, more than 170 of the height of the students, you have to judge in 8,000 results, the worst can traverse about 8,000 times, this low efficiency

If you change the order of the Where condition fields in the above SQL statement, the following:

Select * from Student where height >in and Age >;

Then the result will be:

1. The first is to check out the height of 170 students, the result is only 10;

2. Then in these 10 results to find out the age of more than 25 years old students, so that the number of traversal has been reduced a lot of a lot

Summary: So, do not assume that the order of the fields in the where statement can be arbitrarily random, should be combined with specific circumstances to arrange the order, in order to make more efficient,

Of course, if you want to improve efficiency, you should make an index on both fields (out of the question: index establishment and under what conditions the index will be called)

MySQL optimization--where The order of conditional fields affects efficiency (02)