在大型MySQL数据集中搜索部分单词的最佳方法 [英] Best way to search for partial words in large MySQL dataset

问题描述

我一直在寻找关于stackoverflow的问题，但没有找到一个很好的答案.

I've looked for this question on stackoverflow, but didn't found a really good answer for it.

我有一个MySQL数据库，其中包含一些表，其中包含有关特定产品的信息.当最终用户在我的应用程序中使用搜索功能时，它应该在特定列中搜索所有表.

I have a MySQL database with a few tables with information about a specific product. When end users use the search function in my application, it should search for all the tables, in specific columns.

由于联接和许多where子句的执行效果不佳，我创建了一个存储过程，该过程将这些表中的所有单个单词拆分成列，然后将它们插入表中.它是"word"和"productID"的组合. 该表现在包含超过330万条记录.

Because the joins and many where clauses where not performing really well, I created a stored procedure, which splits all the single words in these tables and columns up, and inserts them in the table. It's a combination of 'word' and 'productID'. This table contains now over 3.3 million records.

目前，如果我对整个单词或单词的开头(例如'searchterm％')匹配，都可以进行快速搜索.这很明显，因为它现在使用索引.

At the moment, I can search pretty quick if I match on the whole word, or the beginning of the word (LIKE 'searchterm%'). This is obvious, because it uses an index right now.

但是，我的客户想搜索部分单词(例如'％searchterm％').这根本没有执行.另外，也不选择FULLTEXT搜索，因为它只能搜索单词的开头，后跟一个通配符.

However, my client want to search on partial words (LIKE '%searchterm%'). This isn't performing at all. Also FULLTEXT search isn't option, because it can only search for the beginning of a word, with a wildcard after it.

那么像这样的搜索功能的最佳实践是什么?

So what is the best practice for a search function like this?

推荐答案

MySQL不适用于文本搜索.使用其他软件可以做到这一点.例如，使用Sphinx为文本搜索索引数据.它将做得很好，并且设置非常简单.如果您使用MySQL 5.1，则可以使用sphinx作为引擎.

MySQL is not well tailored for text search. Use other software to do that. For example use Sphinx to index data for text search. It will do a great job and is very simple to set up. If you user MySQL 5.1 you could use sphinx as an engine.

还有其他服务器比Spinx更好地执行文本搜索，但是它们并非免费的，或者需要安装其他软件.

There are other servers for performing text search better than Spinx, but they are eather not free or require other software installed.

您可以阅读有关以下内容的更多信息: ElasticSearch，Sphinx ，Lucene，Solr，Xapian.哪种适合哪种用法?

You can read more about: ElasticSearch, Sphinx, Lucene, Solr, Xapian. Which fits for which usage?

这篇关于在大型MySQL数据集中搜索部分单词的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！