Alexander Gelbukh Alexander Gelbukh - 7 months ago 14
SQL Question

MySQL: join with an unused table increases execution time?

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?

Jim Jim
Answer

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