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.

If you have MyISAM tables then you can use MATCH..AGAINST keywords to search a string in multiple columns. Check my answer it will give you the idea of how to use MATCH..AGAINST in query — Saharsh Shah
– Saharsh Shah, Commented Jan 10, 2013 at 14:57

Lieven Keersmaekers · Accepted Answer · 2013-01-09 09:48:35Z

2

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.

edited Jan 9, 2013 at 9:48

answered Jan 9, 2013 at 8:01

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Angel M. Over a year ago

yes, must have been right - the results are not ok. I tried what you suggested, but it is the same. I assume, Left join would be better?

Lieven Keersmaekers Over a year ago

@Angel M - I haven't suggested anything, merely showed you the error in your original statement. I don't know your use case but I think you should replace the LEFT JOIN with an FULL OUTER JOIN to get results from either t1 or t2.

Angel M. Over a year ago

I meant - showing me the error - and I found a way - and now I had the right results.

Saharsh Shah · Accepted Answer · 2013-01-10 12:22:37Z

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

edited Jan 10, 2013 at 12:22

answered Jan 9, 2013 at 8:45

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Collectives™ on Stack Overflow

mysql query LIKE

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related