mysql - Subpartitioning several partitions in a table -

i came here find solutions lot of times , found response fit perfect me. , decided write first question.

is first time have mount db partitioning not simple partitioning, why didn't found solution in mysql official documentation.

i have table:

create table systemevents  (     id int unsigned not null auto_increment primary key,     receivedat datetime null,     devicereportedtime datetime null,     facility smallint null,     priority smallint null,     fromhost varchar(60) null,     message text,     ntseverity int null,     eventsource varchar(60),     eventuser varchar(60) null,     eventcategory int null,     eventid int null,     infounitid int null ,     syslogtag varchar(60),     eventlogtype varchar(60), ) 

and need make partitions "fromhost" key:

partitiom list (fromhost) (    partition p01 values in ('server1'),    partition p02 values in ('server2'),    partition p03 values in ('server3', 'server4'),    partition p04 values in less maxvalue ); 

until here it's ok. here comes difficult part:

once main table partitioned several servers, need separate content of every partition subpartitions date field (receivedat), week if posible.

the reason is, database has store events of servers in company entire year, , both "server1" , "server2" creates near 3 millons rows every month. of queries use need first server , then, once have the list of events filtered choosen server, search date, find event looking for.

so, can indicate me how script create subpartitons partition p01 , p02, sorted receivedat field?

if there not understood, please tell me , rephrase it.

thanks lot.

regards. xavidpr

all columns used partitioning have part of primary key if table has primary key.

secondly want partition on fromhost varchar column. using varchar limits types of partitioning can use. besides, if table large enough warrant partitions, idea use numeric id host instead of host name. speed things , reduce table size.

a straight forward solution this:

use range partitioning partitioing on datetime. use key or hash partitioning partitioning on host (preferably integer host_id)

since cannot use range on subpartitions, have partition range , sub partition key or hash

in other words partition receivedat , subpartition fromhost