MySQL Forums
Forum List  »  Partitioning

Rebuilding Index on One Partition
Posted by: Tim Ehrhart
Date: November 20, 2008 06:07AM

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,
...

Options: ReplyQuote


Subject
Views
Written By
Posted
Rebuilding Index on One Partition
4803
November 20, 2008 06:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.