Use Filtering for Efficient Joins (Cache Queries)
When performing a join between two or more entities in a query, put the most selective operators before the actual join expression. This makes the join more efficient.
See Optimize WHERE Clause Expressions for more information.
Joins that test for equality (equivalent joins), that is, joins between two entities that use the equals operator (=), perform better than joins that test for inequality (non-equivalent joins), that is, joins using operators such as greater than, less than, and so on. Comparison operators supported for filtering are as follows:
>, >=, <, <=, ==, !=, In, Between, And, Or, Not, Like
Example
In the following example, the two entities Trade and StockTick are joined by matching their respective securityId and symbol. But the query also places the restriction that only TIBX trades and stock ticks are of interest, and only if the trade's settlement status is FULLY_SETTLED. These filters appear before the actual join expression, which is more efficient than if they were placed after the join (t.securityId = tick.symbol).
select tick.symbol,
sum(tick.price) * 1000 / count(*),
avg(tick.volume),
count(*),
t.counterpartyId
from /Trade t, /StockTick {policy: maintain last 1 sliding where symbol = "TIBX"} tick
where t.securityId = "TIBX"
and t.settlestatus = "FULLY_SETTLED"
and t.securityId = tick.symbol
group by tick.symbol, t.counterpartyId
having count(*) > 2;