且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

全文索引MyISAM,单列与多列索引

更新时间:2023-01-29 16:09:53

对于MyISAM:

  FULLTEXT(`column_a`,`column_c`,`column_x`)
$ c对于InnoDB:

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

如果您使用的是5.6或更高版本,您应该转换为InnoDB。


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.

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.