mysql - Subquery is faster? -

mysql - Subquery is faster? -

if need perform complex calculation based on row data, , alias it, in query might need operate on 10 of 10000 rows because of restrictive clauses, best utilize subquery in clause, instead of using single query?

an illustration easier:

select *,complex_calc(t1.c10) a1 t1 c2 > 5 , c3 < 10 , c6 = 4 , c7 > 50 having a1 > 100 limit 1000;

or

select *,complex_calc(ta1.c10) a1 (select * t1 c2 > 5 , c3 < 10 , c6 = 4 , c7 > 50 limit 1000) ta1 having a1 > 100;

which query faster? guess real question - mysql apply clauses before performing complex_calc on rows?

i guess real question - mysql apply clauses before performing complex_calc on rows?

yes

but if closely @ queries first 1 runs complex_calc on rows satisfying clause , taking 1000 of them

on secon 1 takes 1000 rows, run complex_calc on , take having a1 > 100 less 1000 rows

the botton line your queries not identical in output.

in case complex_calc run twice: 1 time every row in having clause , sec time (upto 1000 times beacause of limit) in select part.

i suggest:

select * (select *,complex_calc(t1.c10) a1 t1 c2 > 5 , c3 < 10 , c6 = 4 , c7 > 50) subtbl a1 > 100 limit 1000;

and phone call if need limit here or in subquery

mysql