how group by having limit works

Question

Can someone explain how construction group by + having + limit exactly work? MySQL query:

    SELECT
        id,
        avg(sal)
    FROM
        StreamData 
    WHERE
        ...
    GROUP BY
        id 
    HAVING
        avg(sal)>=10.0 
        AND avg(sal)<=50.0   
    LIMIT 100

Query without limit and having clauses executes for 7 seconds, with limit - instantly if condition covers a large amount of data or ~7 seconds otherwise. Documentation says that limit executes after having which after group by, this means that query should always execute for ~7 seconds. Please help to figure out what is limited by LIMIT clause.

Answer 1

Using LIMIT 100 simply tells MySQL to return only the first 100 records from your result set. Assuming that you are measuring the query time as the round trip from Java, then one component of the query time is the network time needed to move the result set from MySQL across the network. This can take a considerable time for a large result set, and using LIMIT 100 should reduce this time to zero or near zero.

Answer 2

Things are logically applied in a certain pipeline in SQL:

1. Table expressions are generated and executed (FROM, JOIN)
2. Rows filtered (WHERE)
3. Projections and aggregations applied (column list, aggregates, GROUP BY)
4. Aggregations filtered (HAVING)
5. Results limited (LIMIT, OFFSET)

Now these may be composed into a different execution order by the planner if that is safe but you always get the proper data out if you think through them in this order.

So group by groups, then these are filtered with having, then the results of that are truncated.