using LIKE operator in mysql

Question

I want search companies from my company table when i give company name...here am using like operator

eg: saravana stores

it gives the result saravana stores texttiles,saravana stores thanga maligai,etc(which is contained with saravana stroes...coz of using LIKE operator)

Now my problem is when i give lcd projectors in the companyname, also want to fetch the records which are contained with the only projector word...but like operator gave the results with the 'lcd projector'

am making clear?

Answer 1

Try:

WHERE (name LIKE '%saravana%' OR name LIKE '%stores%')

This has two disadvantages:

• It can't use an index so it will be slow.
• It can give you matches you don't want like 'bestorest' matches '%stores%'.

You might want to use a full text search instead. You could also consider an external engine such as Lucene.

Answer 2

If you want proper fultext search, I highly recommend trying Lucene or Sphinx. I know it would get a little complicated, but it's worth it for the end result.

Answer 3

Mark Byers is right.
To get more efficiency
After query dividing to words you can modify search input to get word base and unify searching to get smth lika:
WHERE (name LIKE '%sarava%' OR name LIKE '%stor%')