Solr and MySQL, How to keep an updated index, and, is a DB even needed if it's simple?

Question

I'm a complete beginner with Solr, so bear with me. :)

In my current project I have a very simple DB - just 1 table that contains 4 fields: id, name, subject, msg.

The way I understand, every time a new record is added (or removed), I'd need to add that record to the index, essentially performing two operations: inserting the record into the DB and adding it to the index.

Is this standard procedure, or is there a way to direct Solr to automatically reindex the DB table either at some interval or whenever there are updates?

Also, since the table is so simple, does it even make sense to store this info in the DB? Why not just keep it in the Solr index, considering that I want the records to be searchable by name, subject, and msg?

My setup is Java, Hibernate, MySQL, and Solrj.

Answer 1

Using a database or not really boils down to how long term you want to keep and grow this data. It is much, much easier to corrupt a whole Solr index (and lose all of your data) than it is to corrupt a whole database. Also, Solr does not have great support for modifying a schema without starting with a fresh index. For instance, you could add another field just fine, but you could not change the name or type of a field without wiping out your index.

If you do go with a DB, you can setup Solr to index directly from the DB using DataImportHandler. For your schema, this should be pretty straightforward, but this can get painful quickly as your DB gets more complex. I think there is some advantage to using the Hibernate objects you already have setup and just inserting them using Solrj. The other pain point with DataImportHandler is that it is completely controlled using http. So you need to manage separate cron jobs (or some other code) to handle the scheduling using wget or curl.