Multiple word text search in MySQL

I am tired of search boxes not working when I input multiple words. This isn't really MySQL-specific, but I usually encounter the issue when working with LAMP, so I figure that's how people will search. Just to set the background, in my opinion there are three types of searching techniques for a text search with an open text field. Those three types are based upon the premise that you can enter multiple keywords in a text field. Given that premise, you can search against:

  1. Exact phrase
  2. All keywords
  3. Any keywords

For example, let's assume we have a database table with available shirt sizes and colors. In that table we have the following data:

size color
Small Red
Small Green
Small Blue
Medium Red
Medium Green
Medium Black
Large Red
Large Blue

In too many cases, searching for "black small" returns zero results. This is a less than optimal example, because if you were generally looking for a "black small" shirt, you may not care about the Medium Black or Small in various colors, but this same technique also applies to full text searches for articles or whatever you might be searching.

Following the example, an Exact Phrase search for "black small" returns no results. Not once, in either column, does the phrase "black small" exist. An All Keywords search for "black small" would also return no results, because no single record includes both "black" and "small" (An All Keywords search for "black medium" would yield one result - for the "Medium Black" record). An Any Keywords search for "black small" would return four results; "Medium Black", "Small Red", "Small Green", and "Small Blue". In most cases (IMO), users want either All Keyword or Any Keyword searches.

The usual (broken) SQL search query looks like this (the '%' means ignore anything else in that direction):
SELECT * FROM table t
WHERE t.some_column LIKE '%my_search_phrase%'
OR t.some_other_column LIKE '%my_search_phrase%';

Let's say your search phrase is again "black small". The Exact Phrase query is:
SELECT * FROM shirts s
WHERE s.color LIKE '%black small%'
OR s.size LIKE '%black small%';

That is the query for an Exact Phrase search; and if you want to ensure an exact match of the terms you input, you're finished. As I mentioned, I don't think this is the generally intended search from a user. More often, they want an All Keywords search. That query looks like this:
SELECT * FROM table t
WHERE (t.some_column LIKE '%my%' OR t.some_other_column LIKE '%my%')
AND (t.some_column LIKE '%search%' OR t.some_other_column LIKE '%search%')
AND (t.some_column LIKE '%phrase%' OR t.some_other_column LIKE '%phrase%');

And for the "black small" search phrase, the All Keywords search is:
SELECT * FROM shirts s
WHERE (s.size LIKE '%black%' OR s.color LIKE '%black%')
AND (s.size LIKE '%small%' OR s.color LIKE '%small%');

This query searches each appropriate field to see if any record in the database somehow includes all of the keywords provided (but in any order, and from any column - thus distinct from the exact search).

Finally, the Any Keywords search - the functionality that I think we most often desire when searching. The Any Keywords search looks like:
SELECT * FROM table t
WHERE (t.some_column LIKE '%my%' OR t.some_other_column LIKE '%my%')
OR (t.some_column LIKE '%search%' OR t.some_other_column LIKE '%search%')
OR (t.some_column LIKE '%phrase%' OR t.some_other_column LIKE '%phrase%');

And for the "black small" search phrase, the Any Keywords search is:
SELECT * FROM shirts s
WHERE (s.size LIKE '%black%' OR s.color LIKE '%black%')
OR (s.size LIKE '%small%' OR s.color LIKE '%small%');

This search returns any record in the database that includes any of your search keywords in any of that records columns. I think this is generally the search query you want to implement. A careful eye notices that the WHERE clause parantheses in the Any Keywords search are unnecessary. This is true, but I left them there because it makes your scripting loops a little easier, because the only difference between Any and All searches is the "OR" vs. "AND" presence.

Now go fix your search box functionality.

Comments

Multiple word text search in MySQL — 2 Comments

  1. SELECT * FROM `shirts` WHERE some_column REGEXP ‘(small|black|other)’
    you can also str_replace search changing spaces with pipe
    so :
    SELECT * FROM `shirts` WHERE some_column REGEXP ‘( REPLACE ( search , ” “, “|” ) )’