0

I want to implement Lucene for full text search. I have a table with 6 million records in an sql database. Each minute around thousand new rows will be added from the application.

Index creation in Lucene takes a lot of time. Each time I delete or create a new index from the database because the user needs to see the updated records on search.

How can I have fast enough search capability?

1) I need updated and history values on search 2) The maximum delay should be 2 seconds

  • 1
    If I understand correctly, Lucene is pulling data from the SQL database? Or no? – barrycarter Dec 1 '14 at 17:16
  • I don't know Lucene, but out of my (limited) experience this is usually solved by using a delta index. It is a small, temporary index of most recent changes that gets merged into the main index every 24 hours or so. That's how it works with Sphinx, for example: pat.github.io/thinking-sphinx/deltas.html – Konrad Morawski Dec 1 '14 at 21:55
2

Lucene indexes don't have to be recreated from scratch each time they're changed. Much like your database tables, you can insert new records, delete old ones and affect an update using a delete/add pair.

Most databases have a mechanism which will allow calling external code, and you can leverage this in triggers that run on INSERT, UPDATE and DELETE to maintain your index. Alternately, you can do the updates outside the database as your batches of data arrive, but you risk having (bigger) synchronization problems if something fails mid-stream.

Also, consider whether or not using Lucene is even necessary. Oracle, PostgreSQL and MySQL (and probably others) all have pre-integrated full-text search that may be good enough for your application and won't need much additional work on your part.

|improve this answer|||||
  • I can vouch for Postgres FTS. I use it in a table with a ts_vector column that has a GIN index on it; several million rows, and I get results in milliseconds. – Chris Cirefice Dec 2 '14 at 2:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.