4

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!

|improve this question
6

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.

|improve this answer
  • It'd be nice if you edited the wiki page you edited so it says WTF Lucene actually is – Alec Teal Mar 21 '15 at 2:37
  • I haven't edited the Wiki Entry, but Lucene is a fast, in memory searchable index that you create based on a larger slower database, or you can just create it as being its own database or in memory subset. This is especially useful if you wanted to bring back all music artists that start with the letter Mi. In MsSQL that would make a round trip to the db run the query and return a result. Because it's sitting in memory on the server it avoids the round trip call, but it needs to be replicated on all servers in your cluster depending on your setup, that's where Solr and ElasticSearch shine. – Justin Shield Mar 22 '15 at 22:34
1

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.

|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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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