Geek Answers Handbook

MySQL: order of sentences

Does MySQL query order ordering work? eg

SELECT * FROM TABLE AS t WHERE t.Employee_id = 1 AND t.payout > 500

VS.

 SELECT * FROM TABLE AS t WHERE t.payout > 500 AND t.Employee_id = 1

Also, what if t.Employee_id is indexed and t.payout is not?

+4
source share
3 answers

The order of everything in sql can be reordered by the query planner at its discretion, if arithmetic allows it. This includes AND, OR, inner joins, some cases of outer joins, etc. It can also reorganize IN queries, place subqueries, etc.

So yes, it will find your correct index, etc.

Beware not to let it explode in your face. This statement, for example:

  select y <> 0 and (x / y) > 0;

it never creates a problem, say, in C, but in SQL the scheduler can fully evaluate the right side first and strangle division by a zero error.

If you want to force an order, you need to use the case statement:

  select case when y <> 0 then (x / y) > 0 else false end;

Last note: for massive queries, complete joins and subqueries, planners usually encounter thresholds for which they no longer try to use all possible plans, and achieve a reasonably good query using genetic algorithms. When they do this, the connection order, etc. Counts.

+1
source

Does MySQL query order support? Efficiency?

No.

Closest you will get the order of the columns in the coverage index (more than one column for one index) based on the columns going from left to right in the index declaration. It does not matter where the columns are used in the query, as well as most of the columns in the coverage index present in the query to start using.

What if t.Employee_id is indexed and t.payout is not?

The optimizer may decide to use the index - if it does not guarantee that it will be used. Table statistics also affect whether or not to use the index.

But the column order in the WHERE clause does not determine if the index is used.

+1
source
  • Configure queries with EXPLAIN to see for yourself. Always the best option
  • It doesn’t matter, the MySQL optimizer will check it for you and give you the best result that he might think about.
0
source

More articles:


All Articles