CODEWITHSUNDEEP
mysqlsqlindexing
ace
ace

Reputation: 12024

How to create MYSQL FULLTEXT index for very large table?

I have a table in MYSQL database that is about 45GB and my disk available space is 30GB. The table type is MyISAM and MySQL version is 5.6. I want to create FULLTEXT on two columns of the table called name and text where name is varchar(255) and text is longtext.

Will MYSQL create temporary table of about the same size as the size of the table ( 45GB) when creating index?

Which is better to use:

ALTER TABLE ADD FULLTEXT or CREATE FULLTEXT INDEX in above context?

Upvotes: 1

Views: 2430

Answers (1)

Rick James
Rick James

Reputation: 142208

The two commands are the same (one maps to the other). It will make a full copy of the table with all its new indexes. So, it is impossible since you have less free disk space than even the table size.

Get bigger machine? Remove other stuff? Do other cleanup?

Note further, the FT index may be close to 45GB, itself. So, even if you could create the index, it might overflow your 30GB.

InnoDB's FULLTEXT seems to create a much smaller index. But still, converting to InnoDB cannot be done without more than 45GB free space.

Upvotes: 2

Related Questions