SAVAFA February 2016

Fulltext Indexing on MyISAM, single column vs multiple column indexing

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.

Answers


Rick James February 2016

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.

Post Status

Asked in February 2016
Viewed 3,151 times
Voted 10
Answered 1 times

Search




Leave an answer