• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Ordering Search Results

 
Matthew Busse
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Ranchers,

I have a problem and I'm not sure where to start. I'm using Hibernate and Spring to access a MySQL database. Right now, I have the search set up such that for multi-word searches, if any of the words is a match, that result is returned. What I want to do is order the search results so that any results that contain all the words are on top, followed by results that contain some of the words, then results that contain only one word. I don't know enough about Hibernate and SQL to know where to start looking, so if someone could set me in the right direction, that would be great.

Thanks!
Matthew
 
Karthik Shiraly
Bartender
Posts: 1210
25
Android C++ Java Linux PHP Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some solutions...choose based on how accurate your results need to be, how consistent the search data should be, and on size of data in DB.

Option 1: Use SELECT...MATCH...AGAINST full text search capability of MySQL.
Not sure if this is what you're using right now.
It orders results based on how many terms match.
No code changes required in the application - just change the SQL.
But it has lot of caveats - the table has to be MyISAM (which means no transactions), and requires a special FULLTEXT index consisting of all searchable columns.
It's a quick and simple solution, but search accuracy is not so great, it's not very tunable, places large load on db server, and becomes slow for large table sizes, or large columns.
More info here http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

Option 2: Use Hibernate Search
This uses the proven lucene text search framework alongside Hibernate automatically.
The text indexing of Lucene is very powerful, with capabilities like word stemming, synoyms and stop words.
Each term that is present in the row will boost the relevance score of that row, and you can get results by order of relevance.
Results will be very accurate.
Suitable if search index needs to be transactionally consistent at all times with DB. No explicit updates to seach index are needed - Hibernate automatically does that when you update table.
Very few code changes required in the application.
Database server load is reduced because actual searching is handled by lucene. Suitable for large data.
http://www.hibernate.org/subprojects/search.html

Option 3: Use a dedicated search server like Apache solr, ElasticSearch, OpenSearchServer, etc
All these are scalable solutions based over lucene, and results are exactly what plain lucene gives.
They mostly run as separate server processes, but some like Solr are also embeddable in process.
These should be deployed if data sizes are large and scalability is an issue.
Has impact on application architecture and deployment.
Explicit coding is needed to send queries and handle search results.
If you have large data sizes, you can take a look into one of these.
I usually use eventual consistency design - the index gets updated lazily after any DB update.
I've used only Solr among these, and I love it!
 
Matthew Busse
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks!

From a quick read, I think option 2 sounds best for my current needs, I'm going to look into that.

Many thanks for your help, I greatly appreciate it!

Matthew
 
Karthik Shiraly
Bartender
Posts: 1210
25
Android C++ Java Linux PHP Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic