How does one insert records based on some kind of meta data or key in particular table belonging to a merge table? I have a network logging program and would like to partition the table so that analysts can query certain tables belonging to the merge table instead of the whold table to corelate events. /*merge table section of email */ Based on my limited research I thought that I would create a merged table such as: /*from the web site */ mysql> CREATE TABLE t1 ( -> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> message CHAR(20)); mysql> CREATE TABLE t2 ( -> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> message CHAR(20)); mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1'); mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2'); mysql> CREATE TABLE total ( -> a INT NOT NULL AUTO_INCREMENT, -> message CHAR(20), INDEX(a)) -> TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
/* trigger section of email */ Question(s): I will assume of that when 5.02 becomes stable and triggers are available then mysql> CREATE TRIGGER ins_sum INSERT ON total IF message='happy ending' Then INSERT INTO t1 (message) VALUES ('goldie locks'),('mama bear'),('eat her up') ELSE INSERT INTO t2 (message) VALUES ('KILLED Mama bear'),('papa bear and baby bear'), ('in cirus'); Will triggers support insert, update, and delete verbs(actions) on a merged table? /* table rotation */ Second question: As tables grow can can a new table be created and automatically added to the merged table, at or on a particular date or time or when the size of the table reaches a certain limit? Which leads to the third question: if myisam tables can be created dynamically based on size of table or time of day, can the oldest table be compressed with myisampack automatically? Thank you, Raymond