http://starrocks.io logo
Join Slack
Powered by
Hey <@U0A71G31CDV> I have two tables with a speed ...
# questions-and-troubleshooting
g
Hey @Rocky I have two tables with a speed int that I want to us a complex formulate to compare and generate a scalar value on. I also need to limit the results based on this score. Is it better to add the complex formula again in the ON clause or use a HAVING clause and just look at the results of the formula?
r
For the best performance and query clarity in StarRocks, you should follow these guidelines when dealing with complex formulas and filtering: 1. Prefer 
WHERE
over 
HAVING
 If you are not performing any aggregation (no 
GROUP BY
or 
SUM/AVG/COUNT
), use the 
WHERE
 clause rather than 
HAVING
. * Performance: 
WHERE
filters rows as early as possible. While StarRocks' optimizer is smart, 
HAVING
is semantically intended for post-aggregation filtering. Using it for row-level filtering can sometimes bypass certain predicate pushdown optimizations. * Standard Practice: Using 
WHERE
for non-aggregated filters is standard SQL and makes the query plan easier to read. 2. Join Type: Equi-join vs. Non-equi-join The choice of where to put the formula depends on whether you have a simple "join key" (like an ID): * Scenario A: You have a Join Key (Best Practice) If your tables share a common ID, join them using that ID in the 
ON
clause first. This allows StarRocks to use a Hash Join, which is highly efficient. Then, place your complex formula in the 
WHERE
clause.
Copy code
sql
    SELECT a.id, b.id, (a.speed _ 0.5 + b.speed _ 0.2) AS score
    FROM table_a a
    JOIN table_b b ON a.user_id = b.user_id -- Equi-join first
    WHERE (a.speed _ 0.5 + b.speed _ 0.2) > 100;
* Scenario B: The Formula IS the Join Condition If there is no join key and the formula is the only way to relate the tables, StarRocks will have to perform a Nested Loop Join (effectively a cross-product). In this case, putting the formula in the 
ON
clause or the 
WHERE
clause is functionally identical for an 
INNER JOIN
, but it will be significantly slower than an equi-join. 3. Use a CTE/Subquery for Readability If the formula is truly complex and you want to avoid writing it twice (once in 
SELECT
and once in 
WHERE
), use a Subquery or CTE. This allows you to name the "score" and then filter on it:
Copy code
sql
WITH CalculatedScores AS (
    SELECT a.id, b.id, (complex_formula_here) AS score
    FROM table_a a
    JOIN table_b b ON a.join_col = b.join_col -- Use an equi-join if possible!
)
SELECT * 
FROM CalculatedScores
WHERE score > 50;
Summary Recommendation: * Avoid 
HAVING
 unless you are using 
GROUP BY
. * Use 
ON
for equality keys (
a.id = b.id
) to ensure a Hash Join. * Use 
WHERE
 for the complex score filter. * Check the Profile: Run 
EXPLAIN
on your query. If you see 
NESTLOOP JOIN
, performance will be poor on large tables. If you see 
HASH JOIN
, the optimizer successfully used your join keys. References • knowledge/usage/best-practice.md • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • using_starrocks/caching/query_cache.md • using_starrocks/accelerate-cardinality-preserving-joins.md
In StarRocks, the best practice is to use a CTE (Common Table Expression) or a Subquery combined with a 
WHERE
 clause. You should avoid using 
HAVING
for row-level filtering, and putting the complex formula in the 
ON
clause can have significant performance side effects. Here is the breakdown: 1. Avoid 
HAVING
for Scalar Filters The 
HAVING
clause is semantically intended for filtering after aggregation (
GROUP BY
). * Performance: 
WHERE
is processed much earlier in the execution plan. StarRocks can "push down" 
WHERE
predicates to the storage engine, allowing it to skip reading unnecessary data. 
HAVING
usually forces the engine to process all rows before filtering them out. * Best Practice: Only use 
HAVING
when you are filtering the result of an aggregate function like 
SUM()
or 
COUNT()
. 2. Use a CTE or Subquery (Recommended) Since StarRocks (like standard SQL) does not allow you to use a column alias in the 
WHERE
clause of the same 
SELECT
level, a CTE is the cleanest approach. It allows you to define the formula once and filter on it later:
Copy code
sql
WITH CalculatedResults AS (
    SELECT 
        t1.id, 
        t2.id,
        (t1.speed * 0.8 + t2.speed * 1.2) / 2 AS score -- Formula defined once
    FROM table1 t1
    JOIN table2 t2 ON t1.id = t2.id -- Use an equi-join for performance!
)
SELECT * 
FROM CalculatedResults
WHERE score > 50; -- Filter on the scalar value here
3. The "ON" Clause Trap Adding a complex formula that compares columns from both tables into the 
ON
clause can be dangerous: * Equi-join vs. Non-equi-join: StarRocks is highly optimized for "equi-joins" (e.g., 
t1.id = t2.id
), which use a fast Hash Join. * Nested Loop Joins: If your 
ON
clause only contains a complex formula (e.g., 
ON complex_func(t1.speed, t2.speed) > 10
), StarRocks cannot use a Hash Join and will revert to a Nested Loop Join. This is essentially a cross-product and is extremely slow on large tables. * Rule of Thumb: Use the 
ON
clause for equality keys to link the tables, and use the 
WHERE
clause (or a CTE filter) for your complex comparison logic. Summary Recommendation 1. Join the tables on a common key (ID, etc.) in the 
ON
clause. 2. Calculate the formula in the 
SELECT
list. 3. Filter the result using a CTE and a 
WHERE
 clause for maximum performance and readability. References • sql-reference/sql-statements/table_bucket_part_index/SELECT.md • knowledge/usage/best-practice.md • sql-reference/sql-statements/keywords.md
3 Views
Open in Slack
PreviousNext
Powered by