I have a table called 'Products' where i have nearly 100,000 records. I have a field in the table called 'Product name' which is a varchar(255) field and contains name of products which could be around 5 - 15 word text. I perform search operation on this table with user input keywords... i do like...
select * from products where product_name like '%USER_KEY%' limit 5;
when user search for single term (ex. iphone) it would look like this...
select * from products where product_name like '%iphone%' limit 5;
This query runs in 0.89 seconds approx which is fine.
when user search for multiple terms (ex. iphone 5gb) it would look like this...
select * from products where product_name like '%iphone%5gb%' limit 5;
This query takes about 4.02 seconds approx. The product name is a unique key indexed already. Can someone help me what can i do on this to get this search to result in lesser time ?
Anything to be done with db ? or any setting change in mysql helps improving query speed ?
