If I need to perform a complex calculation based on row data, and then alias it, in a query that might only need to operate on 10 of 10000 rows because of restrictive where clauses, is it best to use a subquery in the FROM clause, instead of using a single query?
An example is probably easier:
SELECT *,COMPLEX_CALC(t1.c10) AS a1 FROM t1 WHERE c2 > 5 AND c3 < 10 AND C6 = 4 AND c7 > 50 HAVING a1 > 100 LIMIT 1000;
SELECT *,COMPLEX_CALC(ta1.c10) AS a1 FROM (SELECT * FROM t1 WHERE c2 > 5 AND c3 < 10 AND C6 = 4 AND c7 > 50 LIMIT 1000) as ta1 HAVING a1 > 100;
Which query would be faster? I guess the real question is - will MySQL apply the WHERE clauses before performing the COMPLEX_CALC on all rows?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `t1` ADD INDEX `t1_idx_c6_c2` (`C6`,`c2`);
SELECT *, COMPLEX_CALC(t1.c10) AS a1 FROM t1 WHERE t1.c2 > 5 AND t1.c3 < 10 AND t1.C6 = 4 AND t1.c7 > 50 HAVING a1 > 100 LIMIT 1000