How can I search for multiple terms in multiple table columns?

Question

I have a table that lists people and all their contact info. I want for users to be able to perform an intelligent search on the table by simply typing in some stuff and getting back results where each term they entered matches at least one of the columns in the table. To start I have made a query like

SELECT * FROM contacts WHERE
    firstname LIKE '%Bob%'
 OR lastname LIKE '%Bob%'
 OR phone LIKE '%Bob%' OR
 ...

But now I realize that that will completely fail on something as simple as 'Bob Jenkins' because it is not smart enough to search for the first an last name separately. What I need to do is split up the the search terms and search for them individually and then intersect the results from each term somehow. At least that seems like the solution to me. But what is the best way to go about it?

I have heard about fulltext and MATCH()...AGAINST() but that sounds like a rather fuzzy search and I don't know how much work it is to set up. I would like precise yes or no results with reasonable performance. The search needs to be done on about 20 columns by 120,000 rows. Hopefully users wouldn't type in more than two or three terms.

---

Oh sorry, I forgot to mention I am using MySQL (and PHP).

I just figured out fulltext search and it is a cool option to consider (is there a way to adjust how strict it is? LIMIT would just chop of the results regardless of how well it matched). But this requires a fulltext index and my website is using a view and you can't index a view right? So...

Answer 1

I would suggest using MATCH / AGAINST. Full-text searches are more advanced searches, more like Google's, less elementary.

It can match across multiple tables and rank them to how many matches they have.

Otherwise, if the word is there at all, esp. across multiple tables, you have no ranking. You can do ranking server-side, but that is going to take more programming/time.

Depending on what database you're using, the ability to do cross columns can become more or less difficult. You probably don't want to do 20 JOINs as that will be a very slow query.

There are also engines such as Sphinx and Lucene dedicated to do these types of searches.

Answer 2

BOOLEAN MODE

SELECT * FROM contacts WHERE  
MATCH(firstname,lastname,email,webpage,country,city,street...)  
AGAINST('+bob +jenkins' IN BOOLEAN MODE)

Boolean mode is very powerful. It might even fulfil all my needs. I will have to do some testing. By placing + in front of the search terms those terms become required. (The row must match 'bob' AND 'jenkins' instead of 'bob' OR 'jenkins'). This mode even works on non-indexed columns, and thus I can use it on a view although it will be slower (that is what I need to test). One final problem I had was that it wasn't matching partial search terms, so 'bob' wouldn't find 'bobby' for example. The usual % wildcard doesn't work, instead you use an asterisk *.