I have developed a search algorithm, which basically matches records on 3 different criteria types. Name, Address, and Keyword(s).

"doh" , will find:

  • Name: doh
  • Address: re
  • Keywords: me

  • Name: re
  • Address: doh
  • Keywords: me

  • Name: re
  • Address: me
  • Keywords: doh

Now, when two or more records have a match, I am basically prioritising a single found entity, by means of checking how many times it was found.

My problem is, a single query will always only find one record, even it it matches on more than one criteria, so no prioritization can take place. I am currently building a single query, including all the keywords, for all 3 criteria fields. (Multiple AND / OR statements)

My current query looks like this:

Search Term: something somewhere shiny Formulated Query:

select * from table where ((cl_address LIKE @something OR cl_name LIKE @something
OR cl_key_words LIKE @something) OR (cl_address LIKE @somewhere OR cl_name LIKE
@somewhere OR cl_key_words LIKE @somewhere) OR (cl_address LIKE @shiny OR cl_name LIKE 
@shiny OR cl_key_words LIKE @shiny))

To solve this, I would have to run a query for every criteria, for every keyword, for every address field, for every name...

This can result in multiple of 10 queries per search... although it "would" work...

How do the big boys do this?

P.S. The above is my own brain child... and not based on anything other than my own logic.. which may, or may not, be flawed :-)

  • You should look into faceted search with search engines like Apache Solr, Elastic Search or Sphinx. They do exactly what you try to implement (and a lot more). – thorsten müller Feb 24 '14 at 14:51
  • @thorstenmüller , thanks for the comment. I don't think faceted search is where I am heading tho, I am aiming for a direct search, but show more relevant content on top. – Louis van Tonder Feb 24 '14 at 15:02
  • That's exactly what those search engines do. (Maybe you wouldn't even need the facet feature). You can create 'documents' and add lists of keywords to them, then define weights on the lists (documents with more than one hit will get higher scores). So you can not only have the number of hits weighted, but also say that the name (when number of hits otherwise equal) should get a higher weight. That's what you typically use for something like product search on a web site. There are a lot more options. – thorsten müller Feb 24 '14 at 15:07

Your Answer

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

Browse other questions tagged or ask your own question.