'How to index IN/OR in WHERE clause
I have query that use IN and OR in clause and cant manage to use index.
Any ideas how to rework query to use index ?
I tried with UNION as @ErgestBasha suggest but no results found
Query
SELECT DISTINCT name, search_name, priority
FROM search_category_synonymous scs
LEFT JOIN search_category sc ON sc.category_id = scs.category_id
LEFT JOIN category_path cp ON (scs.category_id = cp.path_id)
WHERE
name IN('car red','red car','car','red')
OR
synonymous IN('car red','red car','car','red')
AND sc.status = 1
GROUP BY sc.category_id
Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE scs ALL synonymous NULL NULL NULL 138809 Using temporary; Using filesort
1 SIMPLE sc ref category_id category_id 4 scs.category_id 1 Using where
1 SIMPLE cp ref path_id path_id 4 scs.category_id 1 Using where
Index
ALTER TABLE `oc_search_category`
ADD PRIMARY KEY (`id`),
ADD KEY `category_id` (`category_id`),
ADD KEY `name` (`name`),
ADD KEY `priority` (`priority`);
--------------------------------------------------------------
ALTER TABLE `oc_search_category_synonymous`
ADD PRIMARY KEY (`id`),
ADD KEY `synonymous` (`synonymous`),
ADD KEY `category_id` (`category_id`);
-----------------------------------------------------------------
ALTER TABLE `oc_category_path`
ADD PRIMARY KEY (`category_id`,`path_id`),
ADD KEY `path_id` (`path_id`,`category_id`);
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|