PostgreSQL's and MySQL's full text search

Question

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?

Answer 1

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.

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

Answer 2

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)

Answer 3

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

Here is the documentation: 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.

Answer 4

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

Karl

Answer 5

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).

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).

Answer 6

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

I've no experience using postgresql full text search.

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

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