1

Does the order of the column name assignation in the join part have an impact on indexes ?

For example, if I have an index on table X column Y, and I write:

SELECT * FROM B LEFT JOIN X ON X.Y = B.Y

Then the Y index from table X is used. However, if I write the same query like this:

SELECT * FROM B LEFT JOIN X ON B.Y = X.Y 

will the index still be used?

  • 1
    I don't see any reason for this to be closed. It's a clear, succinctly answerable question with broad real-world relevance: if the order of arguments to the = operator can affect query planning, then it's something worth tinkering with when trying to optimise a query, and if it can't, then it isn't. The selected close reason seems patently inapplicable here. – Mark Amery Sep 26 '16 at 17:21
  • 1
    Also, the order of AND clauses in ON or WHERE does not matter. – Rick James Sep 27 '16 at 17:02
3

In general, no. Inner and Outer joins are not affected by the order tables and columns in the query. This can be confirmed by the result of explain as @ypercube mentioned.

However, if you join using "STRAIGHT_JOIN", then the order of the tables (not the columns) does matter.

Example:

mysql> explain SELECT * FROM b STRAIGHT_JOIN x ON x.y = b.y;
+----+-------------+-------+-------+---------------+------+---------+--------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref    | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+--------+------+--------------------------+
|  1 | SIMPLE      | b     | index | y             | y    | 5       | NULL   |   24 | Using where; Using index |
|  1 | SIMPLE      | x     | ref   | y             | y    | 5       | so.b.y |    1 | Using index              |
+----+-------------+-------+-------+---------------+------+---------+--------+------+--------------------------+
2 rows in set (0.00 sec)

mysql> explain SELECT * FROM x STRAIGHT_JOIN b ON x.y = b.y;
+----+-------------+-------+-------+---------------+------+---------+--------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref    | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+--------+------+--------------------------+
|  1 | SIMPLE      | x     | index | y             | y    | 5       | NULL   |   24 | Using where; Using index |
|  1 | SIMPLE      | b     | ref   | y             | y    | 5       | so.x.y |    1 | Using index              |
+----+-------------+-------+-------+---------------+------+---------+--------+------+--------------------------+
2 rows in set (0.00 sec)
1

Using explain, as suggested by ypercube, the answer is no, the order does not matter. The index is used in both cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.