mysql query LIKE

Question

I need to select any occurrence of a word (keyword search) on two tables and the query I made is like this:

SELECT t1.fname, t1.lname, t2.* FROM t1 , t2  
     WHERE t2.title LIKE "%test%" 
     OR t2.desc LIKE "%test%" 
     OR t2.inc LIKE "%test%" 
     OR t1.fname LIKE "%test%" 
     OR t1.lname LIKE "%test%" 
     AND t1.c_id = t2.c_id;

As there is a lot of data in database, this particular search (with 'test' keyword) takes several minutes and I'm wondering how to optimize this. I tried with LEFT JOIN but it seems I did it wrong - as the results defers pretty much, but it the query is executed very quickly.

It is like this:

SELECT * FROM t2 AS a 
  LEFT JOIN t1 AS b ON a.c_id = b.c_id 
    WHERE a.desc LIKE '%test%' 
     OR a.title LIKE '%test%' 
     OR a.inc LIKE '%test%' 
     OR b.fname LIKE '%test%'  
     OR b.lname LIKE '%test%';

Any help would be much appreciated ... thanks.

Answer 1

You first statement is not what you intended: theAND clause takes precedence over OR so you have actually written

t2.title LIKE "%test%" 
OR t2.desc LIKE "%test%" 
OR t2.inc LIKE "%test%" 
OR t1.fname LIKE "%test%" 
OR (t1.lname LIKE "%test%" AND t1.c_id = t2.c_id;)

This finally results in a (somewhat skewed) natural join between t1 and t2 returning far to many rows.

Answer 2

Try MATCH..AGAINST to search multiple columns for MyISAM tables:

SELECT * 
FROM t2 AS a 
INNER JOIN t1 AS b ON a.c_id = b.c_id 
WHERE MATCH (a.desc, a.title, a.inc, b.fname, b.lname) AGAINST ('test') 

Check this link MYSQL FULL TEXT SEARCH