CODEWITHSUNDEEP
mysql
richard
richard

Reputation: 547

faster join by selecting relevant rows into an aliased table first?

I read on SO that join is performed before where so I was wondering if it is (always) a good idea to perform a join like so:

SELECT * FROM 
    (SELECT * FROM tbl_1 WHERE field = 'the_value') t
    JOIN tbl_2 USING (joinable_field)

instead of the usual

SELECT * FROM tbl_1 t1 JOIN tbl_2 t2 USING (joinable_field)
WHERE t1.field = 'the_value'

Upvotes: 1

Views: 50

Answers (2)

glglgl
glglgl

Reputation: 91049

With this, you are restricting the execution and evaluation order. t must be created first (maybe "on the fly", maybe physially on disk), and tbl_2 is joined in.

In your concrete case, this shouldn't hurt, however. But I cannot see a benefit, either.

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Where did you read that? JOIN is not performed before WHERE. The results should be returned as if the Join is performed before Where. The Query optimizer is free to choose whatever path of execution finds best.

The advice may have some value with very complex queries and/ot not very good optimizers.

Upvotes: 1

Related Questions