database - MySQL Merge tables - high-traffic and large amounts of data -

database - MySQL Merge tables - high-traffic and large amounts of data -

my work uses mysql (myisam) exclusively info storage. have on 300 webservers , 150 databases. unfortunately i'm in position have write table construction back upwards on 100 1000000 rows in 30 day period. thought is:

high volume inserts (no updates or deletes , @ end of table) 1 row selects data older 30 days thrown away

the best solution seems to have table each day combined merge table selects. there duplicate info indeed, select pull recent row based on timestamp , int field. having 30 tables isn't ideal goes life.

are there inherent flaws approach? there other ways approach missing (we stuck @ 5.0)? table locking huge issue when performing alter table on merge table when new day's table created? have table rotation construction in place if go single table having select info want out of old table new 1 quite slow approaches 100 1000000 rows.

there other technologies out there accomplish in elegant manner, our sales team sold solution , don't have luxury of time.

any input appreciated.

structure:

create table `merge_test_1` ( `date_stamp` long not null, `hash` char(32) not null, `p_id` mediumint(8) unsigned not null, `a_id` mediumint(8) unsigned not null, `b_id` mediumint(8) unsigned not null, primary key (`hash`,`p_id`,`date_stamp`) ) engine=myisam

query example

select b_id,a_id merge_test hash='1' , p_id=1 order date_stamp desc limit 1

if i'm getting core of question indexing fruitless because of high-volume inserts, and, searching based off max(id) doesn't meet criteria... "the select pull recent row based on timestamp , int field."

have tested using view purpose? seems plausible win.

e.g.

create table lotsofdata ( id int unsigned auto_increment, int_val int unsigned, the_timestamp timestamp, primary key(id)); -- create view select id,int_val,the_timestamp lotsofdata the_timestamp = max(the_timestamp) , max(int_val) limit 0,1;

i hope helps. if can supply table construction , query example, i'd help. need more specifics.

mysql database merge