The ICE engine often optimizes internal execution of the query basing on
which columns are to be used at each of the query phases. For example,
if one of the joined tables is used only for filtering out the data,
with its columns not occurring in the SELECT list, it can be omitted by
the engine just after finishing the filtering stage, prior to preparing
the final query results. One can manually optimize the query execution
by helping the engine to employ its abilities in such cases. Let’s
consider the following example:
(1) SELECT t1.a, SUM(t2.b)
FROM t1 JOIN t2 ON t1.key = t2.key
WHERE t1.c>10 GROUP BY t1.a;
Imagine quite a standard case that column t1.key is unique on table t1.
Given that, consider the following instead of (1):
(2) SELECT t1_copy.a, SUM( taggr.tsum )
FROM
( SELECT t2.key AS t2_key, SUM(t2.b) AS tsum
FROM t1 JOIN t2 ON t1.key = t2.key WHERE t1.c>10
GROUP BY t2.key ) taggr
JOIN t1 t1_copy ON t1_copy.key = t2_key
GROUP BY t1_copy.a;
When comparing versions (1) and (2) for the large tables, the ICE
speed-up may be really significant. The key point is that the more time
consuming joining algorithm in the inner query produces output only by
means of the table t2. Then, the outer query simply finishes the work.
This trick will work only for SUM, MIN, MAX, and COUNT(*), but not for
AVG or DISTINCT modifier. (Well, for AVG it’s still possibility to work it out.)
