mysql - Fulltext Indexing on MyISAM, single column vs multiple column indexing -


i have extremely large table (4m+ rows) disk space of more 40gb (14gb data , 28gb index). needed fulltext search on multiple fields both combined , separated, meaning needed make possible fulltext search on both single columns , multiple columns together, below:

for combined search

select `column_a`, `column_b` `table_1` match (`column_a`, `column_c`, `column_x`) against ('+$search_quesry*' in boolean mode); 

for separate search

select `column_a`, `column_b` `table_1` match (`column_a`) against ('+search_query*' in boolean mode); select `column_a`, `column_b` `table_1` match (`column_c`) against ('+search_query*' in boolean mode); select `column_a`, `column_b` `table_1` match (`column_x`) against ('+search_query*' in boolean mode); 

here question. have both following sets defined indexes, cause 24gb+ disk space. did right or 1 set 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 reduced required disk space better performance. better suggestion more welcome. :)

p.s. cardinality numbers seem different column_a when indexed combined , 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 have version 5.6 or later, should convert innodb.


Comments

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

caching - How to check if a url path exists in the service worker cache -

Redirect to a HTTPS version using .htaccess -