MySQL: order of clauses

Does the order of MySQL query clauses effect efficiency? e.g.

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

VS.

SELECT * 
  FROM TABLE AS t
 WHERE t.payout > 500
 开发者_JS百科  AND t.Employee_id = 1

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

---

The order of everything in sql is subject to being re-ordered by the query planner as it sees fit if the arithmetics allows it. This includes AND, OR, inner joins, some cases of outer joins, etc. It can also re-organize IN statements, unnest subqueries, etc.

So yes, it'll find your correct index, etc.

Beware of not letting it blow up in your face, too. This statement, for instance:

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

would never pose a problem in, say, C, but in SQL the planner is completely free to evaluate the right side first and choke on a division by zero error.

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

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

Last note: for massive queries full of joins and subqueries, planners typically bump into thresholds beyond which they no longer try every possible plan, and reach a good enough query using genetic algorithms. When they do, the join order etc. does count.

---

Does the order of MySQL query clauses effect efficiency?

No.

The closest you get would be order of columns in a covering index (more than one column, for a single index), based on the columns going from left to right in the index declaration. It doesn't matter where the columns are used within the query so much as that the left most columns in a covering index are present in the query to trigger use.

what if t.Employee_id is indexed, while t.payout is not?

The optimizer might decide to use the index - having one doesn't guarantee it will be used. Table statistics also impact the decision to use an index or not.

But the order of the columns in the WHERE clause does not determine if an index is used.

---

1. Tun the queries with EXPLAIN to see for yourself. Always the best option
2. It doesn't matter, the optimizer of MySQL will check it for you and give you the best result it can think of.