搜索MySQL文本列的最佳方式

Question

我有产品存储在MySQL数据库中，这是一个Wordpress网站，但是我的数据存储在自定义表中。我需要搜索产品，我目前面临一些性能问题，希望有人能帮助我或为我指路。

因为我每天收到一个文件(*.csv)来更新我所有的产品(添加、更新或删除产品)，所以我有一个进程来读取该文件并填充/更新表。在这个过程中，我增加了一个步骤来过滤数据，并将任何特殊字符替换为"不特殊"。字符(例如:用'a'代替'')。

到目前为止，我有一个与product的表(products)相关的表(products_search)，并以此为基础构建，我使用这个表进行搜索。当用户搜索某些内容时，我修改输入以替换特殊字符，因此搜索将直接在表上进行。

问题:在"文本"中搜索列很慢，甚至在列上添加索引也很慢。我正在搜索如下:

select * from products_search
where description like %search_word_1%
or description like %search_word_2% ... 

如果我得到一个结果，我将得到ID和与产品表的关系，并得到我可能需要显示给用户的所有信息。

查找解决方案:我正在寻找一种方法，以更好的性能搜索products_search表。据我所知，wordpress搜索引擎只对"post"起作用。表格有什么办法可以更快地搜索吗?也许使用插件或者只是改变搜索的方式。

Thanks to all

Answer 1

我认为我们需要修改每晚加载，以使索引创建更高效。

我假设:

• CSV文件中的数据替换已有的数据。
• 您愿意使用FULLTEXT进行搜索。

那么做:

1. CREATE TABLE new_data (...) ENGINE=InnoDB;
2. LOAD DATA INTO new_data ...;
3. 清理new_data中的数据。
4. ALTER TABLE new_data ADD FULLTEXT(...);这里要索引的列要么已经存在，要么是在步骤1或3中添加的。
5. RENAME TABLE real_data TO old_data, new_data TO real_data;
6. DROP TABLE old_data;

请注意，这基本上使real_data的停机时间为零，因此您可以继续执行SELECTs。

您还没有解释如何将单个CSV文件喷入wp_posts和wp_postmeta。这听起来像是埋藏在我内心深处的噩梦。

FULLTEXT比wp_postmeta快得多。(我不知道是否有一个现有的方式或插件来实现这样。)

使用' FULLTEXT(description)，你的代码片段将使用

WHERE MATCH(description) AGAINST ('word1 word2' IN BOOLEAN MODE)

而不是非常慢的LIKE开头通配符

如果必须使用wp_postmeta，我建议使用https://wordpress.org/plugins/index-wp-mysql-for-speed/