Senior LevelSQL & MySQL
Compare Hash Aggregation versus Stream (Sort) Aggregation in SQL execution engines.
❓ Interview Prompt: Compare Hash Aggregation (in-memory hash table grouping) vs Stream Aggregation (presorted index input processing), memory usage, and performance.
Database engines use 2 primary algorithms for executing 'GROUP BY' aggregations:
1
Stream (Sort) Aggregation: Requires input rows to be presorted (e.g. via a B-Tree Index). Reads rows sequentially, computing aggregate totals on-the-fly. Extremely fast with $O(1)$ memory usage!
2
Hash Aggregation: Builds an in-memory hash table storing aggregate states per group key. Used when input rows are unsorted. Fast, but requires $O(K)$ memory (spills to disk temporary tables if hash table exceeds memory buffers!).
Code Implementation Example
sql
-- Stream Aggregation (Uses B-Tree Index presorted order: Extra: Using index)
EXPLAIN SELECT category_id, COUNT(*) FROM products GROUP BY category_id;
-- Hash Aggregation (Unsorted input: Extra: Using temporary; Using filesort)
EXPLAIN SELECT brand_name, COUNT(*) FROM products GROUP BY brand_name;💡Inspecting Stream Aggregation vs Hash Aggregation in EXPLAIN.
🎯 Interview Key Takeaway
Provide B-Tree indexes on GROUP BY columns to enable fast Stream Aggregation and eliminate temporary disk tables.
#SQL#Hash Aggregation#Stream Aggregation#Optimizer#Senior
Back to all SQL & MySQL Questions→