Can GROUP BY still be optimized after all these years?

Neat! For others, the data structure they are describing is a count-min sketch. It looks like they're using it to set a bound on elements that can enter the group-by and using it to skip a bunch of extra work

Their clickbench results look really good.

https://benchmark.clickhouse.com/#system=+liH|Hoaus|DcD|ucqs...

You know, I have a hard time seeing how it can be cheaper to run a dataset across a wire rather than just send its grouped results. That's really the problem. I mean, who cares if it takes 100ns to group by on a box when it will take 10 times much for the data to get to it and back.

The fastest possible group by is in sequential memory on that box, fetched from an nvme.

So, when we do group by partitioning, we're really saying, put the groups themselves on different boxes, and group the group by. If it were some utter megaset, the place to put it is on a mainframe, which is basically created for purposes of group by on ridiculous datasets.

[dead]

More importantly, WHY is mysql GROUP BY still broken as default? MySQL is the PHP of databases, but it seems they have no plan to fix bugs.