Hibernate Search, Lucene or any other alternative?

Question

I have a query which is doing ILIKE on some 11 string or text fields of table which is not big (500 000), but for ILIKE obviously too big, search query takes round 20 seconds. Database is postgres 8.4

I need to implement this search to be much faster.

What came to my mind:

1. I made additional TVECTOR column assembled from all columns that need to be searched, and created the full text index on it. The fulltext search was quite fast. But...I can not map this TVECTOR type in my .hbms. So this idea fell off (in any case i thaught it more as a temporary solution).

2. Hibernate search. (Heard about it first time today) It seems promissing, but I need experienced opinion on it, since I dont wanna get into the new API, possibly not the simplest one, for something which could be done simpler.

3. Lucene

In any case, this has happened now with this table, but i would like to solution to be more generic and applied for future cases related to full text searches.

All advices appreciated!

Thanx

Lima

Answer 1

I would strongly recommend Hibernate Search which provides a very easy to use bridge between Hibernate and Lucene. Rememeber you will be using both here. You simply annotate properties on your domain classes which you wish to be able to search over. Then when you update/insert/delete an entity which is enabled for searching Hibernate Search simply updates the relevant indexes. This will only happen if the transaction in which the database changes occurs was committed i.e. if it's rolled back the indexes will not be broken.

So to answer your questions:

1. Yes you can index specific columns on specific tables. You also have the ability to Tokenize the contents of the field so that you can match on parts of the field.

2. It's not hard to use at all, you simply work out which properties you wish to search on. Tell Hibernate where to keep its indexes. And then can use the EntityManager/Session interfaces to load the entities you have searched for.

Mississippian

thanx for explanations, one more short question, I want to be able to search on few string fields. Does it make sense to store all the other fields to the index as well, but not make searchable, and then when i have hit, that i get the object from there, or should i just get the IDS and go to database to get them ? – Lima

@Lima You should only index the fields that you want to search on. You tell Hibernate Search what the @DocumentId (also the @Id) of the indexed entity is. Hibernate will then use this id to get the entity from the database (or the session cache) without you worrying about it. In effect Hibernate Search takes a search string and returns you domain entities matching that search. Neat huh? – Mississippian

Answer 2

Since you're already using Hibernate and Lucene, Hibernate Search is an excellent choice.

What Hibernate Search will primarily provide is a mechanism to have your Lucene indexes updated when data is changed, and the ability to maximize what you already know about Hibernate to simplify your searches against the Lucene indexes.

You'll be able to specify what specific fields in each entity you want to be indexed, as well as adding multiple types of indexes as needed (e.g., stemmed and full text). You'll also be able to manage to index graph for associations so you can make fairly complex queries through Search/Lucene.

I have found that it's best to rely on Hibernate Search for the text heavy searches, but revert to plain old Hibernate for more traditional searching and for hydrating complex object graphs for result display.

Winegar

Answer 3

I recommend Compass. It's an open source project built on top of Lucene that provider a simpler API (than Lucene). It integrates nicely with many common Java libraries and frameworks such as Spring and Hibernate.

Sclater

Answer 4

I have used Lucene in the past to index database tables. The solution works great, but remeber that you need to maintain the index. Either, you update the index every time your objects are persisted or you have a daemon indexer that dump the database tables in your Lucene index.

Have you considered Solr? It's built on top of Lucene and offers automatic indexing from a DB and a Rest API.

Dogwatch

thanx. we already use lucene for document indexing, so i thaught better stick to the same library.How would it be possible with Lucene to, for example, I want to index some relations of objects? Do i must index entire tables, or i could do particular columns that i need from main table and some of its relations? – Lima

The way I did it, was to use SELECT queries with JOINS to create a "flat" structure of my data so that I could run an indexer on them. This is one approach. You can also use stored procedures to flatten your data into a special table used for indexing purposes – Dogwatch

Answer 5

All the projects are based on Lucene. If you want to implement a very advanced features I advice you to use Lucene directly. If not, you may use Solr which is a powerful API on top of lucene that can help you index and search from DB.

Lacerated

i will not need too advanced features i think, but would like to avoid to use new library which we dont use so far. I am not sure I understood why you recommend Solr - which is in any case built on lucene? Could you bit more clarify please ? Thanx you!!! – Lima

I give you an example : you have to make http calls to a web server. In java there is socket library that helps you do that, but there is better : apache commons http client. It's exactly that come with built in libraries that implement the protocol. The same thing for Solr that have a built in API to manage indexes, easy full text search with easy database integration and designed to be run of a servlet container. – Lacerated

Answer 6

A year ago I would have recommended Compass. It was good at what it does, and technically still happily runs along in the application I developed and maintain.

However, there's no more development on Compass, with efforts having switched to ElasticSearch. From that project's website I cannot quite determine if it's ready for the Big Time yet or even actually alive.

So I'm switching to Hibernate Search which doesn't give me that good a feeling but that migration is still in its initial stages, so I'll reserve judgement for a while longer.

Megganmeggi