0

I have an extremely large table (4M+ rows) with disk space of more than 40Gb (14Gb data and 28Gb index). I needed fulltext search on multiple fields both combined and separated, meaning that I needed to make it possible to fulltext search on both single columns and multiple columns together, like below:

for combined search

SELECT `column_a`, `column_b` FROM `table_1` WHERE MATCH (`column_a`, `column_c`, `column_x`) AGAINST ('+$search_quesry*' IN BOOLEAN MODE);

for separate search

SELECT `column_a`, `column_b` FROM `table_1` WHERE MATCH (`column_a`) AGAINST ('+search_query*' IN BOOLEAN MODE);
SELECT `column_a`, `column_b` FROM `table_1` WHERE MATCH (`column_c`) AGAINST ('+search_query*' IN BOOLEAN MODE);
SELECT `column_a`, `column_b` FROM `table_1` WHERE MATCH (`column_x`) AGAINST ('+search_query*' IN BOOLEAN MODE);

Here is my question. I have both following sets already defined as indexes, which cause 24Gb+ disk space. Did I do it right or one set is enough?

ALTER TABLE  `table_1` ADD FULLTEXT (`column_a`, `column_c`, `column_x`);

and/or

ALTER TABLE  `table_1` ADD FULLTEXT (`column_a`);
ALTER TABLE  `table_1` ADD FULLTEXT (`column_c`);
ALTER TABLE  `table_1` ADD FULLTEXT (`column_x`);

OR

ALTER TABLE  `table_1` ADD FULLTEXT (`column_a`);
ALTER TABLE  `table_1` ADD FULLTEXT (`column_c`, `column_x`);

This is mainly to reduced required disk space as well as better performance. Any better suggestion is more than welcome. Thanks :)

P.S. The cardinality numbers seem different for column_a when indexed combined and separated.

Rick James
  • 135,179
  • 13
  • 127
  • 222
SAVAFA
  • 818
  • 8
  • 23

1 Answers1

1

For MyISAM:

FULLTEXT (`column_a`, `column_c`, `column_x`)

For InnoDB:

FULLTEXT (`column_a`, `column_c`, `column_x`),
FULLTEXT (`column_a`),
FULLTEXT (`column_c`),
FULLTEXT (`column_x`)

If you have version 5.6 or later, you should convert to InnoDB.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • So if I convert from MyISAM to InnoDB, having 4 index defining instead of one in MyISAM won't take more disk space? Also is that type of defining indexes in InnoDB enough for something like this ``SELECT `column_a`, `column_b` FROM `table_1` WHERE MATCH (`column_a`, `column_c`) AGAINST ('+$search_quesry*' IN BOOLEAN MODE);`` or I need to define a new index? Does order matter in here? – SAVAFA Feb 13 '16 at 17:42
  • InnoDB's FT indexes are much smaller (for reasons I do not understand). – Rick James Feb 13 '16 at 17:46
  • 1
    Suggest you experiment with, say, 10% of your data -- see how big the indexes are, and how many different indexes are really needed, etc. You are reaching into an area where I have only partial knowledge. – Rick James Feb 13 '16 at 17:49