A sql query：
select id from user where age != 20 and age between 18 and 30;
select id from user where age between 18 and 30 and age != 20;
Afaik the MySQL query optimizer generates the same execution plan for both your queries. It uses the index to check only the rows having
age between 18 and 30 then, for each row, it checks the condition
age != 20 (also using the data from index).
The explanation above assumes the values in the
age columns are various. MySQL uses the
age between 18 and 30 condition first because it filters out more rows than
age != 20. However, if the data in the table is not balanced and most of the rows in the table have
age = 20, MySQL will pick this
age != 20 condition first (because it eliminates more rows than the other condition).
I cannot tell how many rows must have
age = 20 in order for the query optimizer use the
age != 20 condition for filtering but I'm sure the percentage has to be high; it could reach the threshold if the persons in your table are selected from a limited group (f.e. students on the same year of study) but it won't reach it for sure if the ages from the table follow the normal distribution.
MySQL uses one condition or the other to filter out as many rows as it can based on the actual data you have in the table when you run the query. It can change the execution plan later, when the data in the table changes.
id is the PK of your table, it has an index on
age and it uses the InnoDB engine then the query above doesn't need to read the table data. Both the
WHERE conditions and the
SELECT expressions can be evaluated using the index information. I would tell that no further optimization is possible for it.