MySQL queries and text search

Question

I have this query:

select name, body
  from news
 where body like %MyWord%;

I use MySQL database engine. this query will return name, body when found MyWord in body text.

My problem here is that, when I search about two word in body text, like MyWord1 , MyWord2. or more !!!

How I can do that if you know that this query is calling by function (That I can not modify that query all time).

Answer 1

If you need more functionality on your text search patterns, you should use FULL-TEXT-SEARCH in MySQL with the proper indexes.

You will be able to search two or more words at once if that's what you are needing.

Answer 2

You could use some evil SQL-injection (kind of) :)

I assume you pass "MyWord" to the function that contains the query you can't ("can't" or "don't like to"??) change. What happens, if "MyWord" looks something like this:

MyWord1% OR body like %MyWord2

Evil, I know, you have been warned ;)

Answer 3

if you want to find two different words you can do this:

select name, body
  from news
 where body like %MyWord1% and body like %MyWord2%;

however, that will soon become very non-performant, and there are a number of other options you can look at:

• using fulltext search, as suggested by Pablo Santa Cruz (probably the simplest to use)
• parse your body field into a subtable via a trigger upon insert
• search the data by populating a lucene/solr index and searching off of that (a bit more complicated to setup and maintain but the the performance is very impressive.