Mixed Lucene / MySQL Query or Concept

Question

Our project requires near-real time searches and constant updating. The data is currently stored in a MySQL database and the Lucene index is updated as the database is modified.

We have the search capability currently where we want it. However, we are attempting to add the ability to "tag" documents in the index/database. Since the data pots can be millions of records, we don't want to update the Lucene index for tagging (or if there is a way to mass-update Lucene that might work too). We instead have a table of document IDs in MySQL that we would like to be using to determine the tag sets.

The best option I've so far found is to retrieve both list of IDs as an integer array, sort them (so I only need to loop through once), then loop through and look for matches between the two (though this isn't ideal since we possibly lose sorting).

Attempting to use the list of Lucene IDs in "IN" query in MySQL fails because the number of documents can be in the millions and MySQL chokes on it.

Any insight into how we could optimize this or do it?

Another suggestion was a 2nd index and using a MutliSearcher, but I'm not entirely sure how to go about doing that due to still needing to update the index with a possible million rows when updating or deleting a tag set.

Answer 1

For your "mass updates", can't you perform a delta-update to the Lucene index based on a timestamp or similar in your MySql table? I've done this in solr, rather than directly in Lucene, but as Solr is a wrapper around Lucene functionality, this is essentially the same (or so I'm assuming...).

Solr Delta import command.

Relevant question, (perhaps).

Answer 2

For all that follows, the assumption is that you don't have enough RAM to completely hold an entire collection.

Indexing technology is designed in particular for a situation where you have far more reads than writes. It would be good to first analyze the corresponding frequencies and thus quantify "constant updating".

If the frequency of updates is too high, you might want to try to handle this part of the search directly with your database system (if MySQL doesn't do the job, there is also PostgreSQL; also the response speeds will depend on the indexing mechanisms in the DB and the memory available to cache them in-memory).

Otherwise, you may want to look into Solr (which is a bit more than just a simple wrapper around Lucene, as it provides extra functionality that may be based on, but is not by itself available using Lucene).

In particular:

• Solr performance factors
• Multi Core approach (i.e. core swapping)
• StreamingUpdateSolrServer

Maybe you can use different strategies depending on the batch size of the update and the performance off-trade for commits/optimization. For huge batch updates, it may be easier to copy a standby core, batch update, commit/optimize and swap the core. However, it won't be "near-real time" (NRT) any longer; the idea of NRT in Lucene is local and dependens directly on available RAM and collection sizes.