I have a site search query right now that is noticeably slower than most queries on our site despite similar sizes. My search queries use LIKE comparisons where other queries are direct match or at least utilize some indices. I would love to utilize a full text search for the columns I am searching, but the columns are on a variety of tables (6 or so). Is there anything I can do to speed up these queries, or am I stuck with sluggish performance?
SELECT <cols, no evil stars here>
FROM products
<multiple joins snipped>
WHERE products.active = 1
AND (
products.code LIKE '%acme%'
OR products.name LIKE '%acme%'
OR designer.name LIKE '%acme%'
OR collection.name LIKE '%acme%'
OR category.name LIKE '%acme%'
OR parentcategory.name LIKE '%acme%'
OR product_size.prompt LIKE '%acme%'
OR fabric_type.fabric_name LIKE '%acme%'
)
AND (
products.code LIKE '%blue%'
OR products.name LIKE '%blue%'
OR designer.name LIKE '%blue%'
OR collection.name LIKE '%blue%'
OR category.name LIKE '%blue%'
OR parentcategory.name LIKE '%blue%'
OR product_size.prompt LIKE '%blue%'
OR fabric_type.fabric_name LIKE '%blue%'
)
AND (
products.code LIKE '%widget%'
OR products.name LIKE '%widget%'
OR designer.name LIKE '%widget%'
OR collection.name LIKE '%widget%'
OR category.name LIKE '%widget%'
OR parentcategory.name LIKE '%widget%'
OR product_size.prompt LIKE '%widget%'
OR fabric_type.fabric_name LIKE '%widget%'
)
Just for reference, a standard (ie. faster) query might look something like:
SELECT <cols, no evil stars here>
FROM products
<multiple joins snipped>
WHERE products.active = 1
AND designer.id = 2
AND (
category.cat_id = 1
OR parentcategory.cat_id = 1
)
Open to any suggestion, but there are (of course) limitations to how much I can rewrite.