MySQL Forums
Forum List  »  Partitioning

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

Hi,

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:)

Ming

Options: ReplyQuote


Subject
Views
Written By
Posted
Any difference in no-condition-group-by between on partition table and non-partition table?
3989
April 13, 2011 09:46PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.