Mysql – Does the order of the column name assignation in the join part have an impact on indexes in MySQL

indexjoin;MySQL

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?

Best Answer

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)
Related Question