Analysis of mysql joint index and Where clause optimization, mysql clause

Analysis of mysql joint index and Where clause optimization, mysql clause

Problem description:
The sorting and conditions are removed one by one for testing. The result shows that the sorting is in the sorting part. The execution time is changed from 48 seconds to 0.3x seconds.
Therefore, the fields involved in sorting form a joint index alter table xx add index indexname (x1, x2, x3). After two minutes of creating a new index, execute the same SQL statement, the execution time is changed to 0.28 S.
 
For example
Where col1 = ??? And col2 = ???
We recommend that you create an index: (col1, col2)

If you need where col1 = ?? Order by col3
We recommend that you create an index: (col1, col3)

Several other commonly used SQL statements have been optimized in the same way, and the effect is obvious.
After 30 minutes, I checked the slow SQL record file and found that a good SQL statement had become too slow. Why?

Cause analysis:
The union index is added, and there is an or in this SQL statement. When this or statement is switched to union, the problem is eliminated.

Appendix, the execution sequence of a Where clause:

When I used MySQL to query the database, I connected many applications and found it was very slow.

For example:
 

Sample Code for copying: SELECT... WHERE p. required ages_id = 1 AND m. required ages_id = 1 AND c. required ages_id = 1 AND t. required ages_id = 1 AND p. products_id IN (472,474)
 

In this way, the query takes more than 20 seconds, although the index is created on each field. By analyzing Explain SQL, tens of thousands of data entries are returned during the first analysis:
WHERE p. Required ages_id = 1, and then narrow the range according to the conditions in sequence.

After changing the location of the WHERE field, the speed is significantly improved:
WHERE p. products_id IN (472,474) AND p. Required ages_id = 1 AND m. Required ages_id = 1 AND c. Required ages_id = 1 AND t. Required ages_id = 1
In this way, the first condition is p. products_id IN (472,474), which returns less than 10 results, and then Filters Based on other conditions, which naturally improves the speed.

Practice summary:
Do not think that the field order in the WHERE clause does not matter. You should filter out most useless data for the first time as much as possible, and only return the minimum range of data.