Any difference in no-condition-group-by between on partition table and non-partition table?
Posted by: Ming Lu
Date: April 13, 2011 09:46PM
I'm trying to do a group-by aggregation on a 100M rows table. There is no where-condition, which means mysql has to scan all rows in the table anyway.
But it is very slow, since most of time is spent on "Copying to tmp table on disk". I guess since the temporary table is too large, system with small memory has to swap in/out frequently.
My question is that is there any difference when group-by between on partitioning table and non-partitioning table? If I use a column range to split the table into different partitions, there are two approaches to group-by:
1. just as the non-partition table, mysql scan all rows in all partitions and do aggregation then.
2. mysql do aggregation on each partition independently, then merge the sub-result.
The 2nd approach, I think, would consume less memory, since the column has smaller cardinality, the temporary table would be smaller. Is this understanding right?
Actually, same question on merge table since mysql says merge table can "Obtain more speed", although I have no idea on how mysql do it.
Thanks a lot in advance for your response:)