All data in my database should be searchable - MySQL vs Lucene

All the data in my database should be searchable from within my web app. It's lots of data, more than 2 million records.

So what should I do?

1) Should I index all of the data and th开发者_Go百科en use Lucene esclusively to query what I need and therefore not use MySql at all?

2) Or should I use Lucene only for searching and MySQL for complex data associations?

I mean I could still use Lucene for associations, but maybe that's an overkill.

So what's the best approach for dealing with this sort of scenario?

Lucene (and most nosql databases) is not a relational database - so even if you are using trivial ORM, you're going to run into difficulties trying to build even a fairly trivial application exclusively around Lucene.

So I'd suggest there's no way of avoiding using mysql for the core data.

(2 million records is not a HUGE amount of data)

So the question then becomes whether it's worth the effort of building a method for publishing the data out of MySQL into Lucene. Bear in mind that MySQL offers full text indexing and replication out of the box, and there are add-ons like sphinx specifically designed to resolve the shortcomings of the standard solution.

This then covers a lot of the same ground as the 'related' questions I see on my screen currently - I suggest you have a look at through the answers - particularly this one

Take a look at Apache SOLR which is based on Lucene technology.

I don't have much experience with Lucene, but it sounds to me like it doesn't support table associations as well as MySQL, so I'd argue that MySQL is better in the long run.

By default everything is searchable in MySQL unless you remove permissions, which you may eventually want to do depending on how the growth of your database proceeds.