Re: Subpartitioning on number of rows in partition(column) --AND-- Aggregate Query Execution

[Jonathan Stephens]

Hi,

You can alter the partitioning (and subpartitioning) of a table on the fly.

If you wanted to do so based on the number of rows in the table, you could likely do this using an INSERT trigger.

Pruning can work with HASH or KEY partitions or subpartitions, but only for queries using integer columns - you'd have to store the datetime values as Unix timestamps (not MySQL TIMESTAMP which is for this purpose the same as DATETIME) or suchlike.

As for the the bulk inserts, I don't know of any reason why you can't perform those on a partitioned table, and doing so would not affect the operation of any triggers you might have on the table. (In other words, if you've an INSERT trigger on table t1, then INSERT INTO t1 (id, name) VALUES('', 'Larry'), ('', 'Curly'), ('', 'Moe'); causes the trigger to fire 3 times - once for each row that's inserted.)

Jon Stephens
MySQL Documentation Team @ Oracle

MySQL Dev Zone
MySQL Server Documentation
Oracle