Fine-TuningMySQLFull-TextSearchwithInnoDB_MySQL

来源：懂视网责编：小采时间：2020-11-09 20:04:09

Fine-TuningMySQLFull-TextSearchwithInnoDB_MySQL

Fine-TuningMySQLFull-TextSearchwithInnoDB_MySQL:If you are using FULLTEXT indexes in MySQL and plan to switch from MyISAM to InnoDB then you should review the reference manual section onFine-Tuning MySQL Full-Text Searchto see what configuration changes may be required. As I mentioned in

推荐度：

点击下载本文 文档为doc格式

导读Fine-TuningMySQLFull-TextSearchwithInnoDB_MySQL:If you are using FULLTEXT indexes in MySQL and plan to switch from MyISAM to InnoDB then you should review the reference manual section onFine-Tuning MySQL Full-Text Searchto see what configuration changes may be required. As I mentioned in

If you are using FULLTEXT indexes in MySQL and plan to switch from MyISAM to InnoDB then you should review the reference manual section onFine-Tuning MySQL Full-Text Searchto see what configuration changes may be required. As I mentioned inyesterday’s postwhen comparing query results on my database with FULLTEXT indexes in MyISAM versus InnoDB I got different results. Specifically, the InnoDB tables were returning fewer results for certain queries with short FULLTEXT search terms. Here’s an example of a query that returned fewer results on InnoDB:

select idfrom flite.ad_indexwhere match(name,description,keywords) against('+v1*' IN BOOLEAN MODE);

The issue was that all of the fine tuning I had done before was limited to MyISAM, so it didn’t affect InnoDB. In the past I configured MySQL FULLTEXT search to index words as short as 1 character (the default is 3), and to index common words (not to use any stopword list). These are the relevant variables I set in in my.cnf:

ft_min_word_len = 1ft_stopword_file = ''

InnoDB has its own variables to control stopwords and minimum word length, so I needed to set these variables when I changed the tables from MyISAM to InnoDB:

innodb_ft_min_token_size = 1innodb_ft_enable_stopword = OFF

Since those variables are not dynamic, I had to restart MySQL for them to take effect. Furthermore, I needed to rebuild the FULLTEXT indexes on the relevant tables. This is howthe manualinstructs you to rebuld the indexes:

To rebuild the FULLTEXT indexes for an InnoDB table, use ALTER TABLE with the DROP INDEX and ADD INDEX options to drop and re-create each index.

Rather than drop and recreate the indexes, I just usedALTER TABLE ... FORCEto rebuild the table (and indexes), like this:

alter table flite.ad_index force;

After making those changes I re-ranpt-upgrade, and now I am getting the same set of rows back from MyISAM and InnoDB. The order of the rows is slightly different in some cases, but as I mentioned yesterday that isexpected behavior.

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

Fine-TuningMySQLFull-TextSearchwithInnoDB_MySQL

推荐度：

点击下载本文 文档为doc格式

标签： my mysql fu

热门焦点

Fine-TuningMySQLFull-TextSearchwithInnoDB_MySQL

Fine-TuningMySQLFull-TextSearchwithInnoDB_MySQL

Fine-TuningMySQLFull-TextSearchwithInnoDB_MySQL

最新推荐

猜你喜欢

热门推荐