全文搜索是答案吗?

问题描述

好的，我有一个看起来像这样的mySQL数据库

OK I have a mySQL Database that looks something like this

ID-所记录的整数和唯一ID

标题-物品的名称

说明-项目说明

我想同时搜索关键字的标题和描述.

I want to search both title and description of key words, currently I'm using.

选择*从标题为LIKE％key％

这有效并且因为数据库中没有太多内容，但是搜索"this key"并没有找到"this that key"，我想改善网站的搜索引擎，甚至可以添加某种形式排名系统(但要花很长时间了).

And this works and as there’s not much in the database, as however searching for "this key" doesn’t find "this that key" I want to improve the search engine of the site, and may be even add some kind of ranking system to it (but that’s a long time away).

关于这个问题，我听说过全文搜索"(据我所知)是数据库设计的主要内容，但是作为这个主题的纽比，我对此一无所知……

So to the question, I’ve heard about something called "Full text search" it is (as far as I can tell) a staple of database design, but being a Newby to this subject I know nothing about it so…

1)您认为这会有用吗?

1) Do you think it would be useful?

还有另外一个questron…

And an additional questron…

2)我能从数据库设计/搜索引擎设计中学到什么，这将为我指明正确的方向.

2) What can I read about database design / search engine design that will point me in the right direction.

如果具有相关性，则该网站目前是用坚固的PHP(即没有框架)编写的(我想到了将其转换为Ruby on Rails的想法)

If it’s of relevance the site is currently written in stright PHP (I.E. without a framework) (thro the thought of converting it to Ruby on Rails has crossed my mind)

更新

谢谢，我将进行全文搜索.对于后来发现此问题的任何人，我都找到了很好的教程，也适用于全文搜索.

Thanks all, I'll go for Fulltext search.And for any one finding this later, I found a good tutorial on fulltext search as well.

推荐答案

'％keyword％'类型搜索的问题在于，即使您创建索引，也无法有效地在常规表中对其进行搜索在该列上.考虑一下您如何看待电话簿中的字符串.实际上，没有办法对其进行优化-您必须扫描整个电话簿-而MySQL就是这样做，即进行全表扫描.

The problem with the '%keyword%' type search is that there is no way to efficiently search on it in a regular table, even if you create an index on that column. Think about how you would look that string up in the phone book. There is actually no way to optimize it - you have to scan the entire phone book - and that is what MySQL does, a full table scan.

如果您将搜索更改为关键字％"并使用索引，则可以非常快速地进行搜索.听起来这不是您想要的.

If you change that search to 'keyword%' and use an index, you can get very fast searching. It sounds like this is not what you want, though.

因此，我已经使用全文索引/搜索了很多，这里有一些优点和缺点:

So with that in mind, I have used fulltext indexing/searching quite a bit, and here are a few pros and cons:

专业人士

• 很快
• 返回按相关性排序的结果(默认情况下，尽管可以使用任何排序方式)
• 可以使用停止词.

缺点

• 仅适用于MyISAM表
• 太短的单词将被忽略(默认最小为4个字母)
• where子句中需要不同的SQL，因此您将需要修改现有查询.
• 不匹配部分字符串(例如，"word"不匹配"keyword"，仅匹配"word")

这里是一些有关全文搜索的好文档.

另一种选择是使用搜索系统，例如 Sphinx .它可以非常快速和灵活.它针对搜索进行了优化，并与MySQL很好地集成.

Another option is to use a searching system such as Sphinx. It can be extremely fast and flexible. It is optimized for searching and integrates well with MySQL.

这篇关于全文搜索是答案吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！