Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Suppose you want to create a web page with some search capabilities over a database. Search has to be restricted both on some categorization of data and open text given by the users. Functional and non-functional requirements pose this constraints:

  • Search has to be fast
  • Data representation paradigm (i.e. Relational, Object-Relational, Semistructured...) should support big modifications on every stage of software lifetime.
  • Database's paradigm should permit easy refactoring for existing client code

Having this into account

  1. Would you recommend a relational database? How would you tackle the constraints in this scenario?
  2. Would you recommend a XML database? How would you tackle the constraints in this scenario?
  3. If you think above solutions to be not useful in the light of this problem... what could be a useful approach?

Please give some bibliography (or some websites) supporting your answers.

thanks!

share|improve this question
1  
add comment

2 Answers 2

up vote 6 down vote accepted

Lucene Index

Generally speaking, some of the big website (think server and database clusters) applications that I've written, I've used a Lucene/Lucene.NET index to create a search index of appropriate database fields. The main advantage of this is speed, an in memory db index as opposed to a round trip -> db -> webserver for a search.

From a code development perspective lucene is completely separate from your database implementation and so you have a separation of concerns between how you want to represent your data, and how you index your data for easy searching. You can use a NoSQL db, Relational DB, etc. The search engine wont care as it's only getting its results from the index, and only when you need to display the actual contents you can grab the contents by an indexed field on the db and you're done.

See the search engine on:

There are actually 2 lucene indexes behind the scenes, 1 index which is created specifically for predictive text (results returned are based on most popular searched on artist at the time), and the full db index which indexes albums, artists which match the search criteria.

So far the performance of the Lucene Index is exlemprarly, I have > 1 million db entries indexed by artist and album and can return N-Gram results data back < 1ms for the predictive text index.

share|improve this answer
add comment

Whether or not to use SQL or Lucene for searching depends on how sophisticated you need for your searching to be.

  • If you don't need keyword based search, then stick with SQL.
  • If you are interested in a more intelligent ordering of the results, then use Lucene.
  • If you need keyword based search across a large amount of data, then use Lucene.

If you do decide to go with Lucene, then Embedding Lucene in Your Service gives advice to developers on how to start. You will still keep your data in SQL but you will also keep another copy of the searchable parts in Lucene. There is more here on SQL vs Lucene. It also addresses how to use Lucene in multi-threaded environments.

share|improve this answer
    
There are a few other options, such as if you have a controlled vocabulary and a need for very precise searching (when an RDF/SPARQL engine can be more suitable). Some SQL databases come with keyword-based searching too; the division isn't as simple as you make out. (For the record, we use all of the above in our production web applications, depending on the data and the searches…) –  Donal Fellows May 4 '13 at 19:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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