|
|
|
|
| |
CREATE TABLE log_CCYY
(
dt DATETIME NOT NULL,
info VARCHAR(100) NOT NULL,
INDEX (dt)
) TYPE = MYISAM;
If the current set of log tables includes log_1999, log_2000, log_2001, log_2002, and log_2003, you can set up a MERGE table that maps onto them like this:
CREATE TABLE log_all
(
dt DATETIME NOT NULL,
info VARCHAR(100) NOT NULL,
INDEX (dt)
) TYPE = MERGE UNION = (log_1999, log_2000, log_2001, log_2002, log_2003);
The TYPE option must be MERGE, and the UNION option lists the tables to be included in the MERGE table. After the table has been set up, you query it just like any other table, but the queries will refer to all the constituent tables at once. The following query determines the total number of rows in all the log tables:
SELECT COUNT(*) FROM log_all;
This query determines how many log entries there are per year:
SELECT YEAR(dt) AS y, COUNT(*) AS entries FROM log_all GROUP BY y;
Besides the convenience of being able to refer to multiple tables without issuing multiple queries, MERGE tables offer some other nice features:
A MERGE table can be used to create a logical entity that exceeds the allowable size of individual MyISAM tables.
You can include compressed tables in the collection. For example, after a given year comes to an end, you wouldn't be adding any more entries to the corresponding log file, so you could compress it with myisampack to save space. The MERGE table will continue to function as before.
Operations on MERGE tables are similar to UNION operations. UNION is unavailable prior to MySQL 4, but MERGE tables can be used in some cases as a workaround.
|
|
|
|
|
|
| Link Partners: Asia florist, Flowers to India, Hong kong flowers, Site submit, Cheap web hosting, China florist, Japan florist |
|
