List:General Discussion« Previous MessageNext Message »
From:Krishna Chandra Prajapati Date:May 13 2008 10:18am
Subject:Re: index creation taking too much time
View as plain text  
Hi anand,

PRIMARY KEY  (`id`),
 KEY `KI_IDX_0805090456` (`words`,`id`),
 KEY `CI_IDX_0805090456` (`lf_id`)

Since id is a primary key. Then why again indexing on id is being created
(`words`,`id`). It will be a duplicate index on id.  words is a varchar
type. So instead of creating fulltext index restrict word(15)). Try. Key
buffer seems to be OK.

No free key blocks are there. This can be the reason.
Key_blocks_unused                 0



On Mon, May 12, 2008 at 6:48 PM, Ananda Kumar <anandkl@stripped> wrote:

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



-- 
Krishna Chandra Prajapati

Thread
index creation taking too much timeAnanda Kumar12 May
  • Re: index creation taking too much timeKrishna Chandra Prajapati13 May
  • Re: index creation taking too much timemos13 May
    • Re: index creation taking too much timeAnanda Kumar13 May
  • Re: index creation taking too much timeKrishna Chandra Prajapati13 May
    • Re: index creation taking too much timeAnanda Kumar13 May
      • Re: index creation taking too much timeMoon's Father20 May