Search code examples
sqlfull-text-searchsearch-engine

Can someone explain me which queries I need for a nice search in SQL?

I asked for a single query in my previous question but now I want to ask about multiple queries.

I'm making a search engine that searches through some specific tables.

When the input is "testone testtwo testthree" I will need to do a lot of queries.

  1. a search with the complete string
  2. a search with the 3 words in 3 strings ( if they can be seperately found in one item)
  3. a search with 2 words in a combined string
  4. a search with 2 words in 2 strings
  5. a search with all words apart

As you know this decides what is a more important result.

I am building the queries seperately for all the 5 searches above as well as seperate for each table because a result from one table can be more important than from the other.

I made the queries like this: 

SELECT *
FROM company_address
WHERE address_street LIKE '%test%'
OR address_zipcode LIKE '%test%'
OR address_telephone LIKE '%test%'"
OR address_fax LIKE '%test%'
OR address_website LIKE '%test%'

I know this is not the best way to do it, and because you guys are much better than me with this i would love some advice!

Solution

  • Even if you could do all of this in one query, it would be hard, if doable, to differentiate which result is more important(relevant) in that case.. so you either do requests separately, or go for some completely different solution. Looks like you could use soemthing like Sphinx search server (it is used in conjuction with your RDBMS, MySQL most probably), it's opensource:

    http://sphinxsearch.com/

    Sphinx can search for phrases and sort results by relevance.