CODEWITHSUNDEEP
mysqljoindatabase-designrelational-database
Ratatouille
Ratatouille

Reputation: 1492

Join predicate order

I'm always be amused and confused(at same time) whenever I have been to asked prepare and run Join query on Sql Console.

And the cause of most confusion is mainly based upon the fact whether/or not the ordering of join predicate hold any importances in Join results.

Example. 

SELECT  "zones"."name", "ip_addresses".*
    FROM  "ip_addresses"
    INNER JOIN  "zones" ON "zones"."id" = "ip_addresses"."zone_id"
    WHERE  "ip_addresses"."resporg_accnt_id" = 1
      AND  "zones"."name" = 'us-central1'
    LIMIT  1;

Given the sql query, the Join predicate look like this.

... INNER JOIN "zones" ON "zones"."id" = "ip_addresses"."zone_id" WHERE "ip_addresses"."resporg_accnt_id"

Now, would it make any difference in term of performance of Join as well as the authenticity of the obtained result. If happen to change the predicate to look like this

... INNER JOIN "zones" ON "ip_addresses"."zone_id" = "zones"."id" WHERE "ip_addresses"."resporg_accnt_id"

Upvotes: 3

Views: 2060

Answers (1)

Arth
Arth

Reputation: 13110

The predicate order won't make a performance difference in your case, a simple equality condition, but personally I like to place the columns from the table I'm JOINing to on the LHS of each ON condition

SELECT ...
  FROM ip_addresses ia

  JOIN zones z
    ON z.id = ia.zone_id 

 WHERE ...

The optimiser can use any index available on these columns during the JOIN and I find it easier to visualise this way.

Any additional conditions also tend to be on columns of the table being JOINed to and I find again this reads better when this table is consistently on the LHS

Not quite the same, but I did see a case where performance was affected by the choice of column to isolate

I think the JOIN looked something like

SELECT ...
  FROM table_a a

  JOIN table_b b
    ON a.id = b.id - 1

Changing this to

SELECT ...
  FROM table_a a

  JOIN table_b b
    ON b.id = a.id + 1

allowed the optimiser to use an index on b.id, but presumably at the cost of an index on a.id

I suspect this kind of query might need analysing on a case by case basis

Furthermore, I would probably switch your table order round too and write your original query:

SELECT z.name,
       ia.* 
  FROM zones z 

  JOIN ip_addresses ia
    ON ia.zone_id = z.id
   AND ia.resporg_accnt_id = 1

 WHERE z.name = 'us-central1' 
 LIMIT 1

Conceptually, you are saying "Start with the 'us-central1' zone and fetch me all the ip_addresses associated with a resporg_accnt_id of 1"

Check the EXPLAIN plans if you want to verify that there is no difference in your case

Upvotes: 4

Related Questions