MySQL: join with an unused table increases execution time?

Question

Assuming t is a large table, and the following two queries

SELECT t1.value, t2.value 
FROM t as t1 JOIN t as t2
ON t1.id = t2.id 
WHERE t1.key = '123'

and

SELECT t1.value, t2.value 
FROM t as t1 JOIN t as t2 JOIN t as t3
ON t1.id = t2.id
WHERE t1.key = '123'

the second one having a JOIN with a table that is not used in the SELECT.

The second query executes much slower. I expected that MySQL would figure out that the third JOIN is not used and will just ignore it. But it does not?

If you want to know anything about the performance and execution of queries then you have to start with the EXPLAN (and/or EXPLAIN EXTENDED) — Strawberry, Jul 16 '15 at 08:14

score 1 · Answer 1 · edited May 23 '17 at 11:59

1

This is because the default JOIN in mySQL implies INNER JOIN. The third join will not be ignored because this will alter the eventual data set you get back after executing the query.

This stackoverflow question contains more detailed information

edited May 23 '17 at 11:59

Community

1
1

answered Jul 16 '15 at 07:58

Tikkes

4,599
4
36
62

The reason is because MySQL performs a CROSS JOIN in this case, and not as you said ... Take a look at my answer . – aleroot Jul 16 '15 at 08:56
CROSS and INNER are synonyms in MySQL. Only difference being that one is used with conditions while the other is not. – Tikkes Jul 16 '15 at 09:04

score 1 · Accepted Answer · answered Jul 16 '15 at 08:11

Your second query doesn't have an ON clause for the second join:

SELECT t1.value, t2.value 
FROM t as t1 
JOIN t as t2 
JOIN t as t3 ON t1.id = t2.id
WHERE t1.key = '123';

This means that every matching record in t1 will be joined onto every record in t2. This is, perhaps, what you meant:

SELECT t1.value, t2.value 
FROM t as t1 
JOIN t as t2 ON t1.id = t2.id 
JOIN t as t3 ON t1.id = t3.id
WHERE t1.key = '123';

This will perform much more reasonably because it isn't creating a huge number of results.

If you intended to do a full join onto t3:

SELECT t1.value, t2.value 
FROM t as t1 
JOIN t as t2 ON t1.id = t2.id 
JOIN t as t3
WHERE t1.key = '123';

Then this will be slower because, even though you are not SELECTing a field from t3 it does change the output because it produces extra rows.

See here for examples http://sqlfiddle.com/#!9/e86c9/3

score 1 · Answer 3 · edited May 04 '16 at 06:49

It is not that the MySQL optimizer isn't smart enough to remove the unused query, it is just that you are using the wrong syntax here. As the documentation states, your query will be performed as:

JOIN t as t2 JOIN t as t3 --> t2 CROSS JOIN t3

The syntax you are using isn't standard SQL and cannot be used in any SQL standard compliant database. Take a look at the specific MySQL JOIN documentation here .

MySQL: join with an unused table increases execution time?

3 Answers3