Bug #1893 Adding an index to a 3.9M row table with many FULLTEXT idxs eats too much space

• Submitted: 19 Nov 2003 15:08
• Modified: 21 Nov 2003 15:29
• Reporter: Larry Hengl
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.16
• OS: Linux (Linux RH 9)
• CPU Architecture: Any

[19 Nov 2003 15:08] Larry Hengl

Description:
Table file is 6GB and the index file is 5GB, 3.9M rows, and has 7 FULLTEXT indexes. When I try to add an index on a column substring it consumes the rest of the 20GB space on tmp and errors out. Same error when I tried to ALTER table ORDER BY...

If I create a new table of just the index column needed, the substr index builds with no problem.

All I can think of is the index build step brings in the entire table+index space (11GB) and then needs more for sorting...(9GB+)?  Does not make sense.

Error msg is:
mysql> alter table sequence order by seqtype;
ERROR 3: Error writing file '/data/tmp/STWMlYfo' (Errcode: 28)

How to repeat:
drop table sequence;
create table sequence ( 
  rn                int unsigned not null primary key, 
  id                varchar (50), 
  seqtype           varchar (50), 
  length            int unsigned, 
  accnum            varchar (50), 
  lastupdate        date, 
  description       text, 
  keywords          text, 
  organism          text, 
  patnum            text, 
  localpatpubdate   date, 
  localpatfiledate  date, 
  localpatnum       text, 
  patassignee       text, 
  assigneecode      varchar (100), 
  inventor          text, 
  derwentid         text, 
  pattitle          text, 
  patpos            text, 
  comments          text
  ) max_rows=100000000 avg_row_length=2000;

#### Run load_data.sql -- 3.9Million rows and lots of text data (6GB load file)

### Add indexes

alter table sequence
  add fulltext(comments),
  add fulltext(organism),
  add fulltext(keywords),
  add fulltext(pattitle),
  add fulltext(description),
  add fulltext(patassignee),
  add fulltext(inventor)
;

### The following line fails
alter table sequence add index (seqtype(7));

### This fails too (see error in bug description)
alter table sequence order by seqtype;

[21 Nov 2003 15:29] Alexander Keremidarski

If you check with SHOW STATUS how ALTER works with your table you will see 
Status = Repair by sorting

| 589517 | root | localhost | bugs | Query   | 91   | Repair by sorting | alter table sequence add index (seqtype(7)) |

so it works as you described.

ALTER TABLE on MyISAM table always recreates all indexes and may need a lot of temporary space.

This is unoptimal design which will probably change in the future releases, but not a bug to be fixed in 4.0

By the way it is not suggested to have more than one Full-text index per table.