Need Help: "LIKE" query optimization


  Can anyone tell me if this is a good usage of "LIKE" query
select p.productid, p.prodname, p.prodcode, c.catname from products p, categories c
where p.productid like "%acne%" or p.prodname like "%acne%" or p.prodcode like "%acne%" or c.catname like "%acne%" ORDER BY RAND();
   using "LIKE" condition in multiple fields to search on something is kinda
   messy to me. Are there any other better idea on how to optimize that query?

   Any help is highly appreciated.


the messiness can be mitigated, but only somewhat, like this…

WHERE CONCAT(p.prodname,p.prodcode,c.catname) LIKE like ‘%acne%’

but as for optimization, no, using LIKE with a leading wildcard is going to remain slow

I see. May I know what will be the best recommended query for the sql code that I provided above? The idea is to search(a keyword(s)) on products(product_id, product_name, product_code) and categories(categories_name) table

Btw, I really appreciate your help.

what you posted is about as good as it gets

i was wondering with your comment on this one

“leading wildcard is going to remain slow”

is there anything i could to make it faster?

I’m not expert but maybe something like Lucene or some other search engine would be faster than MySQL in this case?

That would require some benchmarks.

not without removing the leading wildcard, no

so it should be like something like this(below) to make it faster

LIKE “acne%”

the leading wildcard is now removed.

will that make a great difference in terms of performance/optimization?

LIKE “%foo%” will always be slow in MySQL

LIKE “foo%” is faster, but your not getting all the results you need…

If you really need the “%foo%” foo functionality, you need a good full text search engine.
Try SOLR for that, it’s free and works well with millions of records, on cheap web servers.


but it will also make a huge difference in the results that you get :slight_smile: