2

I have a MYISAM table in MySQL 5.6 with a text column with millions of rows. The total space taken by that table is about 40 GB most of which is in the text column. I started the process 70 plus hours ago with this command:

CREATE FULLTEXT INDEX page_text_idx ON table_name(text_col);

It is still running and initally consumed about 40 GB space and after that slowly eating disk space at about half GB per hour.

Is something wrong and I should wait and MYSQL indexing very slow process?

Does any one know from experience how long it takes per GB of text indexed?

ace
  • 11,526
  • 39
  • 113
  • 193
  • What does `SHOW PROCESSLIST` say while it is running? "by keycache" or "by filesort" or something else? – Rick James Mar 15 '16 at 00:54
  • Since you have 5.6, I wonder why you are not using InnoDB. – Rick James Mar 15 '16 at 00:54
  • @Rick James I ran SHOW PROCESSLIST command and it says: State: Repair with keycache. this in row with Id: 4 and Info: CREATE FULLTEXT INDEX page_text_idx ON table_name(text_col). I am not sure what it means. It is still running third day and using up lot of disk space. Reason for MYIsam: Due to legacy db I had to import from the days when full text indexing was only for MyISam. – ace Mar 15 '16 at 03:43
  • 1
    @ace What happened? Is it still running? – philwilks Feb 04 '19 at 20:31

1 Answers1

0

SET myisam_max_sort_file_size to something significantly larger than the index, but not bigger than the disk space you have. Then see if it will eschew "Repair with keycache" and do it the more efficient way. I'm pretty sure that works for non-FULLTEXT indexes; I don't know about FT.

Rick James
  • 135,179
  • 13
  • 127
  • 222