mysql multi-column search

Question

I have a table like this:

+-------------------+---------------+
| description       | colour        |
+-------------------+---------------+
| Macrame Dress     | Washed Black  |
| Darcelle Gilet    | Dirty Shellac |
| Darcelle Cardigan | Washed Black  |
| Let It Rot Vest   | Optic White   |
| Let It Rot Crew   | Washed Black  |
| Let It Rot Crew   | Optic White   |
| Battalion Short   | Somme         |
| Seine Dress       | Washed Black  |
| Seine Dress       | Cocomotion    |
| Odette V-neck     | Linen Marl    |
+-------------------+---------------+

I want search it for "black dress" and it only return rows 1 & 8. If I type in "black", it will return rows 1, 3, 5 & 8. Dress should return rows 1, 8 & 9.

Can anyone think of a really elegant, beautiful bit of sql that will take any number of search words and return the most concise result set. I can think of some pretty ugly ways to do it, but they upset my karma.

Answer 1

Ideally, I think you would have two separate search terms, one for description (s1) and one for color (s2).

Then your query becomes:

select * from tbl where description like '%(s1)%' and colour like '%(s2)%'

Substituting in the values for s1 and s2 of course.

A blank s2 would result in %% which should match everything (I think).

To be able to search on multiple terms in either field, you need something like

select * from tbl
    where description + colour like '%(s1)%'
      and description + colour like '%(s2)%'
      and description + colour like '%(s3)%'

This will have to be constructed on the fly, based on the number of words in your search pattern (so "black dress" will have s1 and s2, black will just have s1). The "description + colour" bit is concatenated fields; my SQL is a bit rusty so I don't know how you'd do that exactly but the concept is sound.

Answer 2

The only nice solution (karma-wise) I can think of would involve using a FULLTEXT index. This would allow you to make use of queries like this:

SELECT * FROM mytable
WHERE MATCH (description,color) AGAINST ('black dress');

Unfortunately, FULLTEXT indexes require the MyISAM table type, if I recall correctly.

EDIT

I doubt that this is what you are looking for but let me add it for the sake of discussion. You could duplicate your data into a temporary MyISAM table and do you full text search on that:

CREATE TEMPORARY TABLE mytmptable ENGINE=MyIsam SELECT * FROM mytable;
ALTER TABLE mytmptable ADD FULLTEXT KEY (description,color); 
SELECT * FROM mytmptable WHERE MATCH (description,color) AGAINST ('black dress');

Answer 3

What data type are your columns? If they are VARCHAR or similar, you'd simply do

select 
  * 
from 
  tbl 
where 
  description like '%(s1)%' 
  or colour like '%(s1)%'

(This is adapted from @Pax, who I believe misunderstood the question -- I take it to mean it should match whenever the search term appears in 'description' or 'colour'.)

Answer 4

where colour+description like '%s1%'