Rebuilding Index on One Partition
I'm using a partitioned MySQL table (partitioned by day) to store web cache logs for a large organization. Queries work fine with the partitioning, making use of the partition pruning optimizations. I import data (30 million records/day) by turning off keys on the table (ALTER TABLE cachelog DISABLE KEYS), then importing from text files, which is quite fast. However, when I enable keys (ALTER TABLE cachelog ENABLE KEYS), MySQL rebuilds indexes on EACH partition, rather than just the partition that was just updated. Needless to say, building indexes (just source and dest IP as INT) on a few hundred million records is not a trivial task. Is there any way to run DISABLE KEYS on a single partition, and have MySQL build an index ONLY on that partition?
FYI, the table structure is below:
DROP TABLE IF EXISTS `cachelog`;
CREATE TABLE `cachelog` (
`date` date NOT NULL,
`time` time NOT NULL,
`timetaken` int(4) unsigned DEFAULT NULL,
`client_ipaddress` int(4) unsigned DEFAULT NULL,
`exception` varchar(20) DEFAULT NULL,
`filter_result` varchar(20) DEFAULT NULL,
`category` varchar(50) DEFAULT NULL,
`referer` varchar(255) DEFAULT NULL,
`responsecode` int(10) unsigned DEFAULT NULL,
`status` varchar(20) DEFAULT NULL,
`request_type` varchar(10) DEFAULT NULL,
`content_type` varchar(45) DEFAULT NULL,
`uri_scheme` varchar(6) DEFAULT NULL,
`uri_host` varchar(128) DEFAULT NULL,
`port` int(10) unsigned DEFAULT NULL,
`uri_path` text,
`uri_query` text,
`uri_extention` varchar(15) DEFAULT NULL,
`user_agent` varchar(256) DEFAULT NULL,
`language` varchar(45) DEFAULT NULL,
`gateway` int(4) unsigned DEFAULT NULL,
`sc_bytes` int(5) unsigned DEFAULT NULL,
`cs_bytes` int(5) unsigned DEFAULT NULL,
`virusid` varchar(16) DEFAULT NULL,
`server_ipaddress` int(4) unsigned DEFAULT NULL,
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`,`date`) USING BTREE,
KEY `clientip` (`client_ipaddress`),
KEY `serverip` (`server_ipaddress`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (TO_DAYS(date))
(
PARTITION p20081114 VALUES LESS THAN (733725) ENGINE = MyISAM,
PARTITION p20081115 VALUES LESS THAN (733726) ENGINE = MyISAM,
PARTITION p20081116 VALUES LESS THAN (733727) ENGINE = MyISAM,
PARTITION p20081117 VALUES LESS THAN (733728) ENGINE = MyISAM,
...