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

[Ijaz Rashid]

===========
Question 1:
===========
How to perform subpartitioning on size (# of rows) in partition (column). I.E. WHEN NUMBER OF RECORDS FOR "ID=100" EXCEED 1 MILLION, CREATE NEW SUBPARTITION AND INSERT ROWS IN THIS PARTITION, So on....

=========
Detail:
=========
Drop table if exists XA;

CREATE TABLE XA(ID smallint NOT NULL, LogTime timestamp NOT NULL, Value smallint NOT NULL) ENGINE=ARCHIVE
PARTITION BY RANGE(ID)
SUBPARTITION BY HASH(month(LogTime))
(
PARTITION p0 VALUES LESS THAN (1)
(
SUBPARTITION s0,
SUBPARTITION s1
)
);


I'm having one table on which I've defined partition on column "ID" and subpartionion on "HASH(month(LogTime))".

I WANT TO DEFINE SUBPARTIOIN ON SIZE (NO. OF ROWS) OF PARTITIONED COLUMN. I.E. WHEN NUMBER OF RECORDS FOR "ID=100" EXCEED 1 MILLION, CREATE NEW SUBPARTITION AND INSERT ROWS IN THIS PARTITION, So on....


My table contains 100,000 "IDs" and billions of records.
================================
ID LogTime Value
================================
1 2006-12-28 00:00:01 100
1 2006-12-28 00:00:02 101
1 2006-12-28 00:00:03 105
2 2006-12-28 00:00:01 150
2 2006-12-28 00:00:02 151
2 2006-12-28 00:00:03 155
.....
100,000 2006-12-28 00:00:01 180
100,000 2006-12-28 00:00:02 182
100,000 2006-12-28 00:00:03 194

==========
Question 2:
==========
Should i use BULK INSERT to insert 100,000 records per second in my table ?

===========
Question 3:
===========
Subpartition should support aggregate queries e.g.

SELECT SUM(VALUE), AVERAGE (VALUE)
FROM XA
WHERE tagid in (1,2,3,4,5) AND
time >= '2006-12-10 10:10:10'
time <= '2007-01-09 10:10:09'
GROUP BY tagid;


(Thank you for help).