CODEWITHSUNDEEP
mysqlquery-optimization
JonoB
JonoB

Reputation: 5887

MySQL query optimisation - to alias or not?

Is there any major difference from an optimisation point of view between the following two alternatives? In the first option I alias the table, so the total_paid calculation is only run once. In the second option, there is no table alias, but the SUM calculation is required a few times.

Option 1

SELECT tt.*, tt.amount - tt.total_paid as outstanding
FROM
  (
    SELECT t1.id, t1.amount, SUM(t2.paid) as total_paid
    FROM table1 t1
    LEFT JOIN table2 t2 on t1.id = t2.t1_id
    GROUP BY t1.id
  ) as temp_table tt
WHERE (tt.amount - tt.total_paid) > 0
LIMIT 0, 25

Option 2

SELECT t1.id, t1.amount, SUM(t2.paid) as total_paid
  , (t1.amount - SUM(t2.paid)) as outstanding 
FROM table1 t1
LEFT JOIN table2 t2 on t1.id = t2.t1_id
WHERE (t1.amount - SUM(t2.paid)) > 0
GROUP BY t1.id
LIMIT 0, 25

Or perhaps there is an even better option?

Upvotes: 0

Views: 120

Answers (1)

Kevin E.
Kevin E.

Reputation: 59

if you run the queries with EXPLAIN, you'll be able to see what's going on 'inside'.

Also, why don't you just run it and compare the execution times?

Read more on explain here

Upvotes: 1

Related Questions