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
