Fulltext Indexing on MyISAM, single column vs multiple column indexing

Question

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 · Accepted Answer · 2016-02-12 23:53:56Z

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.

answered Feb 12, 2016 at 23:53

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SAVAFA Over a year ago

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?

Rick James Over a year ago

InnoDB's FT indexes are much smaller (for reasons I do not understand).

Rick James Over a year ago

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.

Collectives™ on Stack Overflow

Fulltext Indexing on MyISAM, single column vs multiple column indexing

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related