Is it better to SELECT before JOINING?

Question

I need to join 3 tables a,b,c and I know that only one row from the table most to the left has to appear in the end result.

SELECT * 
    FROM a 
        LEFT JOIN b 
            ON a.id = b.id 
        LEFT JOIN c 
            ON c.id2 = b.id2
    WHERE a.id = 12;

I have come up with the following query because it seems more efficient, but both queries take the same time to execute. Is this because the first query is optimized? Should I bother to choose the more efficient (second) query or stick to the first one because it's more readable?

SELECT * 
    FROM (SELECT * FROM a WHERE id=12) AS temp 
        LEFT JOIN b 
            ON temp.id = b.id 
        LEFT JOIN c
            ON b.id2 = c.id2;

Answer 1

Yes, it is better to SELECT before JOINING.

assume:

1000000 record in table A, 20000 record in table B, and 500000 record in table C

For First Query

1: Read 1000000 record form table A -------------------------- 1000000 I/O

2: Write 1000000 Result + Read 20000 from B --------------- 2020000 I/O

3: Write 2020000 Result + Read 500000 from C ------------- 4540000 I/O

4: Filter Result 4540000 + Write Output 1 --------------------- 9080001 I/O [Total]

---

For Second Query

1: Read 1000000 record form table A -------------------------- 1000000 I/O

2: Filter Result 1000000 + Write 1 ------------------------------ 1000001 I/O

3: Write 1 Result + Read 20000 from B ------------------------ 1020001 I/O

4: Write 1 Result + Read 500000 from C ---------------------- 1520001 I/O

5: Write Output Result 1 ------------------------------------------ 1520002 I/O [Total]

---

Please refer this link

Answer 2

As always with optimizing queries, the answer should be: it depends. The answers depends on several things, among others:

• Is there actually a performance benefit by using query 2 instead of query 1. This can be seen in the query plan that is created for these queries. The created query plan can be the same for both queries, but it can also be different when indices are used.
• It might also depend on the number of rows in the tables that are queried. How long does the query run and how often is the query used. If you start optimizing a query that is used once a day and runs for a few miliseconds, you better use the query that is the best readable.

So the only person that can really determine whether you should use query 1 or query 2 is: You. It is impossible to give you sound advise on this topic.