mysql keyword search across multiple columns

Question

Various incarnations of this question have been asked here before, but I thought I'd give it another shot.

I had a terrible database layout. A single entity (widget) was split into two tables:

CREATE TABLE widgets (widget_id int(10) NOT NULL auto_increment)

CREATE TABLE widget_data ( widget_id int(10), field ENUM('name','size','color','brand'), data TEXT)

this was less that ideal. if wanted to find widgets of a specific name, color and brand, i had to do a three-way join on the widget_data table. so I converted to the reasonable table type:

CREATE TABLE widgets (widget_id int(10) NOT NULL auto_increment, name VARCHAR(32),size INT(3),color VARCHAR(16), brand VARCHAR(32))

This makes most queries much better. But it makes searching harder. It used to be that if i wanted to search widgets for, say, '%black%', I would just SELECT * FROM widget_data WHERE data LIKE '%black%'. This would give me all instances of widgets that are black in color, or are made by blackwell industries, or whatever. I would even know exactly which field matched, and could show that to my user.

how do I execute a similar search using the new table layout? I could of course do WHERE name LIKE '%black%' OR size LIKE '%black%'... but that seems clunky, and I still don't know which fields matched. I could run a separate query for each column I want to match on, which would give me all matches and how they matched, but that would be a performance hit. any ideas?

Answer 1

You probably want to look into MySQL full text search capability, this enables you to match against multiple columns of varchar type.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

Answer 2

You can include part of WHERE expression into selecting columns. For example:

SELECT 
  *, 
  (name LIKE '%black%') AS name_matched,
  (size LIKE '%black%') AS size_matched
FROM widget_data 
WHERE name LIKE '%black%' OR size LIKE '%black%'...

Then check value of name_matched on side of the script.

Not sure how it will affect performance. Feal free to test it before going to production

Answer 3

You have two conflicting requirements. You want to search as if all your data is in a single field, but you also want to identify which specific field was matched.

There's nothing wrong with your WHERE name LIKE '%black%' OR size LIKE '%black%'... expression. It's a perfectly valid search on the table as you have defined it. Why not just check the results in code to see which one matched? It's a minimal overhead.

If you want a cleaner syntax for your SQL then you could create a view on the table, adding an extra field which consists of concatenating the other fields:

CREATE VIEW extra_widget_data AS
  SELECT (name, size, color, brand,
          CONCAT(name, size, color, brand) as all_fields)
  FROM widget_data;

Then you'd have to add an index on this field, which requires more space, CPU time to maintain etc. I don't think it's worth it.

I'm sure (or at least I hope) you have more interesting problems than this to solve, why not just go with what you have and move on?