在SQL中最有效的搜索方式? [英] Most efficient way to search in SQL?

问题描述

我有一个数据库，其中有75,000+行，每天添加500多个条目.

I have a database with 75,000+ rows with 500+ entries added per day.

每行都有标题和描述.

我创建了一个RSS feed，为您提供了特定搜索词的最新条目(例如 http://site. com/rss.rss?q = Pizza 会为搜索词"Pizza"输出RSS.

I created an RSS feed which gives you the latest entries for a specific search term (ex. http://site.com/rss.rss?q=Pizza would output an RSS for the search term "Pizza").

我想知道什么是为此编写SQL查询的最佳方法.现在我有:

I was wondering what would be the best way to write the SQL query for this. Right now I have:

SELECT * 
FROM 'table' 
WHERE (('title' LIKE %searcherm%) OR ('description' LIKE %searcherm%))
LIMIT 20;

但是问题在于执行查询需要2到10秒.

But the problem is it takes between 2 to 10 seconds to execute the query.

有没有更好的方法来编写查询，我是否必须缓存结果(该怎么做?)还是改变数据库结构来加快查询速度(索引?)

Is there a better way to write the query, do I have to cache the results (and how would I do that?) or would changing something in the database structure speed up the query (indexes?)

推荐答案

一个相对简单的解决方案是在这两个字段上合并FULLTEXT索引，然后使用该索引进行搜索.

A relatively simple solution for this would be incorporating a FULLTEXT index on these two fields and subsequently searching by using this index.

ALTER TABLE table ADD FULLTEXT(title, description);

然后您需要执行搜索，请执行以下操作:

Then would you need to perform a search, you'd do the following:

SELECT id FROM table
WHERE MATCH (title, description) AGAINST ('keyterm');

全文索引搜索是大多数SQL数据库中包含的自动解决方案.与执行LIKES相比，速度要快得多.这也针对您的特定情况进行了优化，因为您只对自然语言搜索词感兴趣.

Fulltext indexed search is the automatic solution included in most SQL databases. It's much speedier comparing to doing LIKES. This is also optimized for your specific case because you are only interested in natural language search terms.

同样，全文索引具有一些用于检测相关性的限制算法.您可以在此处

As well, fulltext index has some limiting algorithm for detecting relevancy. You can read more about it here

编辑

在alter语句中，我错过了全文索引名称，它应该是:

In the alter statement, I missed the fulltext index name, it should be:

ALTER TABLE table ADD FULLTEXT ft_index_name(title, description);

这篇关于在SQL中最有效的搜索方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！