3

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!

Community
  • 1
  • 1
Sem
  • 4,477
  • 4
  • 33
  • 52
  • I would say from my experience that mysql (or any RDBMS) is great to maintain reliable data, but not so good for searching. Why not dump your data into something like SOLR and search there? (Yes, I know it is not a 5 minutes solution) – Itay Moav -Malimovka Nov 11 '11 at 16:09
  • @ItayMoav - This would not seem to be suitable as a search facility as part of a live application. – MatBailie Nov 11 '11 at 16:16
  • 1
    "Why not dump your data into something like SOLR and search there?" - because it's usually huge over-kill to suggest something like this as a "first thing to try" – Cylindric Nov 11 '11 at 16:16
  • 1
    @Zundrium, when you say "in 3 strings" do you mean separate fields? Or separately in the same field? – Cylindric Nov 11 '11 at 16:23
  • The only way I have done this type of search was to use dynamic SQL within a stored procedure. – Michael Riley - AKA Gunny Nov 11 '11 at 16:31

2 Answers2

2

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.

Dmitry Avtonomov
  • 8,747
  • 4
  • 32
  • 45
1

In the past I've done some trickery using CASE.

SELECT
    *,
    CASE WHEN address_street LIKE '%test%' THEN 1 ELSE 0 END
    +
    CASE WHEN address_zipcode LIKE '%test%' THEN 1 ELSE 0 END
    +
    CASE WHEN address_telephone LIKE '%test%' THEN 1 ELSE 0 END
    +
    CASE WHEN address_fax LIKE '%test%' THEN 1 ELSE 0 END
    +
    CASE WHEN address_website LIKE '%test%' THEN 1 ELSE 0 END
    AS Score
FROM company_address

That gives you results with a score where the more matches there are, the higher the score, and you can give different scores to different matches. If you repeat the CASE statement in the ORDER BY or WHERE, you can narrow down your results.

This can get unwieldy pretty quick though, so be careful :)

Cylindric
  • 5,858
  • 5
  • 46
  • 68