index creation taking too much time
- Date: Mon, 12 May 2008 18:48:19 +0530
- From: "Ananda Kumar" <anandkl@xxxxxxxxx>
- Subject: index creation taking too much time
Hi All,
We have a table which is around 100 Million rows. Its a myisam table, but
the db default is innodb.
CREATE TABLE `dc_data` (
`id` decimal(22,0) NOT NULL,
`words` varchar(255) NOT NULL,
`lf_id` decimal(22,0) NOT NULL,
`occurence` bigint(20) NOT NULL,
`date_modified` timestamp NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP) ENGINE=MyIsam DEFAULT CHARSET=utf8
indexs are as below
PRIMARY KEY (`id`),
KEY `KI_IDX_0805090456` (`words`,`id`),
KEY `CI_IDX_0805090456` (`lf_id`)
we have 8 cpu, 8 gb ram.
We use set below parameters at session level
myisam_sort_buffer_size=300MB
myisam_max_sort_file_size=10GB
Each index creation is taking 10hrs, is there any way i can speed up index
creation.
regards
anandkl