全文索引MyISAM，单列与多列索引 [英] Fulltext Indexing on MyISAM, single column vs multiple column indexing

问题描述

我有一个非常大的表（4M +行），磁盘空间大于40Gb（14Gb数据和28Gb索引）。我需要在多个字段上进行全文搜索，包括合并和分隔，这意味着我需要在单列和多列上进行全文搜索，如下所示：

用于组合搜索

  SELECT`column_a`，`column_b` FROM`table_1` WHERE MATCH（`column_a`，`column_c`， `column_x`）AGAINST（'+ $ search_quesry *'IN BOOLEAN MODE）; 
  

分开搜寻

  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）; 
  

这是我的问题。我有两个已经定义为索引的集合，这会导致24Gb +磁盘空间。

  ALTER TABLE`table_1` ADD FULLTEXT（`column_a`，`column_c` ，`column_x`）; 
  

和/或

  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`）; 
  

这主要是为了减少所需的磁盘空间以及更好的性能。任何更好的建议都是值得欢迎的。谢谢：）

对于 column_a ，当索引合并和分隔时，基数数字看起来不同。

解决方案

对于MyISAM：

  FULLTEXT（`column_a`，`column_c`，`column_x`）
  
 
 
  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.

                        
这篇关于全文索引MyISAM，单列与多列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！