Search code examples
mysqldatabasequery-optimization

Improve MySQL Query

Apologies if I still use MySQL instead of MySQLI. Will be upgrading this soon but at the moment I need to find a way to solve this.

I have a query which I thought is already optimized, which apparently is not.

I have less than 200 rows in the fruits table yet it the query takes about 15 seconds to complete

Here is the query:

SELECT `o`.`fruits_id` FROM `fruits` as `o` 
JOIN `fruits_categories` as `oc` ON `o`.`fruits_id` = `oc`.`fruits_id` 
JOIN `categories` as `c` ON `c`.`fru_id` = `oc`.`fru_id` 
WHERE ( (o.fruits_name LIKE '%apple orange%' OR o.fruits_description LIKE '%apple orange%' OR o.instagram_tag LIKE '%apple orange%' OR c.cat_name LIKE '%apple orange%' OR p.price_name LIKE '%apple orange%') 
OR ((o.fruits_name LIKE '%apple%' OR o.fruits_description LIKE '%apple%' OR o.instagram_tag LIKE '%apple%' OR c.cat_name LIKE '%apple%' OR p.price_name LIKE '%apple%') 
AND (o.fruits_name LIKE '%orange%' OR o.fruits_description LIKE '%orange%' OR o.instagram_tag LIKE '%orange%' OR c.cat_name LIKE '%orange%' OR p.price_name LIKE '%orange%')) ) 
GROUP BY `o`.`fruits_id`

I'm worried that when the database grows, the search will be unusable as it might take forever.

All help appreciated.

B

Solution
    1. All LIKE '%apple orange%' conditions are unnecessary, they are covered by the conditions for the single words.
    2. like '%whatever%' conditions cannot use indexes. Consider creating a fulltext index on the affected columns and use match(...) against(...) fulltext search instead of like operator with pattern matching.