difference of fulltext index on many columns vs one accumulated column

Question

I just stumbled upon the maximum column restriction of a fulltext index on adding it to many columns:

ALTER TABLE some_table ADD FULLTEXT (col1, col2, col3, col4, col5, col6, ...);

This can result into the following error:

1070 Too many key parts specified; max 32 parts allowed

This means the index can only be spanned over maximum of 32 columns. To work around this, I could simply create a new column and merge those columns contents:

ALTER TABLE some_table ADD merged_fulltext text NOT NULL;
INSERT INTO some_table(merged_fulltext) SELECT CONCAT_WS(col1, col2, col3, col4, col5, col6, ...);
ALTER TABLE some_table ADD FULLTEXT merged_fulltex;

Now there is a fulltext index over one column with merged content.

Of course now I have duplicated data and the some_table column must be updated if any of the merged column's content changes, but regarding to the fulltext index:

Is there any difference by using the merged fulltext index instead of one spanned accross multiple columns? Can I use this as a workaround to overcome the too many key parts limitation?

Im just looking to use the fulltext search MATCH(merged_fulltext) AGAINST(...) always on all columns, so no sorting or searching on just a few columns.

As a side note, you can use `concat_ws()` to add the space between the columns. — Gordon Linoff, Jun 12 '14 at 11:47

score 0 · Accepted Answer · answered Dec 13 '14 at 08:52

On the use case you give, there is no reason why you should not do it. However if your columns content is big, it's a pity to have it duplicated but I believe MySQL does not leave you really the choice. If it grows to be a problem it will still be time to switch to specialised fulltext search engines like Lucene, ElasticSearch (based on Lucene) or SphinxSearch and connect them to MySQL using IDs as reference if needed.

difference of fulltext index on many columns vs one accumulated column

1 Answers1