This tutorial will try to show how easy it is to replace SQL
constructs that use where column like '%something%'
with external full-text index using
Hyper Estraier.
In less then 10 minutes from installation to fill-text search-able table (speed may vary somewhat, but it's really fast).
$ sudo apt-get install postgresql-8.1 postgresql-server-dev-8.1 \ libdbd-pg-perl $ sudo cpan Search::Estraier
$ cd pgestraier $ sudo make install
This will just install library at right place without running tests or examples.
Let's assume you have database called content in which you have table
new which has fields title, lead, content, keywords and source which
should be search-able.
You will have to do:
$ ./bin/pgest-index.pl content --create news > search.sql
That's it. Really. You will even get example SQL query to shoehorn into your application.
You can now connect to database using psql, edit search string in
search.sql and try your new full-text index.
However, you will notice that we indexed every field in table news, which is wasteful (because other fields are ids of various things, booleans and fields which aren't interesting for full-text search).
So, we can do better:
$ ./bin/pgest-index.pl content --create news \ --sql="select id,title,lead,content,keywords from news"
We added custom SQL query which will be used to produce full-text index and triggers so that we touch only columns in which we are really interested.
This time around that it! Enjoy.
For all available options see pgest-index documentation.