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

Jan Doggen
  • 1,140
  • 4
  • 16
  • 22
user3326558
  • 1
  • 1
    If I understand correctly, Lucene is pulling data from the SQL database? Or no? –  Dec 01 '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: https://pat.github.io/thinking-sphinx/deltas.html – Konrad Morawski Dec 01 '14 at 21:55

1 Answers1

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.

Blrfl
  • 20,235
  • 2
  • 49
  • 75
  • 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 02 '14 at 02:37