PostgreSQL和MySQL的全文搜索

How do the full text search systems of PostgreSQL and MySQL compare? Is any clearly better than the oder? In which way are they different?

PostgreSQL和MySQL的全文搜索系统如何比较?有没有明显优于奥得河?它们以哪种方式不同?

6 个解决方案

#1

PostgreSQL 8.3 has built in full text search which is an integrated version of the "tsearch2"

PostgreSQL 8.3内置全文搜索,这是“tsearch2”的集成版本

Here is the documentation: http://www.postgresql.org/docs/8.3/static/textsearch.html

以下是文档:http://www.postgresql.org/docs/8.3/static/textsearch.html

And the example from the documentation:

以及文档中的示例:

SELECT title
FROM pgweb
WHERE to_tsvector(body) @@ to_tsquery('friend');

Where body is a text field. You can index specifically for these types of searches and of course they can become more complex than this simple example. The functionality is very solid and worth diving into as you make your decision.

身体是文本字段。您可以专门为这些类型的搜索编制索引,当然它们可能比这个简单的示例更复杂。功能非常可靠,值得您做出决定。

Best of luck.

祝你好运。

#2

I'm not well versed in PostgreSQL unfortunately, but if you use the FULL TEXT search in MySQL you're immediately tied to MyISAM. If you want to use InnoDB (and if ACID compliance means anything to you, you should be using InnoDB) you're stuck using other solutions.

不幸的是,我不熟悉PostgreSQL,但是如果你在MySQL中使用FULL TEXT搜索,你就会立即与MyISAM联系在一起。如果您想使用InnoDB(如果ACID合规性对您来说意味着什么,您应该使用InnoDB),那么您将无法使用其他解决方案。

Two popular alternatives that are often rolled out are Lucene (an apache project with a Zend module if you're using PHP) and Sphinx.

经常推出的两个流行的替代品是Lucene(如果你使用的是PHP,那么带有Zend模块的apache项目)和Sphinx。

#3

If your using Hibernate as a ORM I highly recommend using Hibernate search. Its build on top of Lucene so its super fast.

如果你使用Hibernate作为ORM我强烈建议使用Hibernate搜索。它建立在Lucene之上,所以它超级快。

Karl

#4

I've had pretty good experience with postgresql/tsearch2, especially since it was rolled into the standard distribution (before version 8.0 - I think - it was an optional contrib feature, and upgrading to tsearch2 involved a bit of work).

我对postgresql / tsearch2有很好的经验,特别是因为它已经进入标准发行版(在8.0之前 - 我认为 - 它是一个可选的contrib功能,升级到tsearch2涉及一些工作)。

If I recall correctly you have to set some properties (fuzzy matching, dictionary stuff) before startup, whereas on other databases those things are flexibly exposed through the fulltext syntax itself (I'm thinking of Oracle Text, here, though I know that's not relevant to your question).

如果我没记错,你必须在启动之前设置一些属性(模糊匹配,字典的东西),而在其他数据库上,这些东西通过全文语法本身灵活地暴露(我在想Oracle Text,虽然我知道这不是与你的问题相关)。

#5

I think you can use Sphinx with both MySQL and Postgres. Here is an article to explain how to use Sphinx with MySQL (you can add it as a plugin)

我认为你可以将Sphinx与MySQL和Postgres一起使用。这篇文章解释了如何将Sphinx与MySQL一起使用(您可以将其添加为插件)

#6

Mysql full text search is very slow. It can't handle data more than 1 million (several tens of seconds per query).

Mysql全文搜索速度很慢。它无法处理超过100万的数据(每个查询几十秒)。

I've no experience using postgresql full text search.

我没有使用postgresql全文搜索的经验。

I have used sphinxsearch. It is very fast and easy to use. But it is not so powerful. I mean the search functionality. For example, it doesn't support like 'abc?', where '?' stands for any character.

我用过sphinxsearch。它非常快速且易于使用。但它不是那么强大。我的意思是搜索功能。例如,它不支持'abc?',其中'?'代表任何角色。

I also know lucene. It is powerful, but it is hard to learn.

我也知道lucene。它很强大,但很难学。

#1