How to rewrite SQL query?

Question

MySQL

I want to speed up the request.
I made a new table oc_product_attribute_id in which I added the text_id index for each text field.
I rewrote the query with text_id and the time compared to searching through LIKE increased by 35 times. What's wrong?
Initial request:

SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 
LEFT JOIN oc_product_to_store p2s ON (p2s.product_id=p.product_id) 
LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=p.product_id) 
LEFT JOIN oc_category_path cp ON(cp.category_id=p2c.category_id) 
<b>LEFT JOIN oc_product_attribute p2a ON (p2a.product_id=p.product_id) 
WHERE p2a.attribute_id IS NOT NULL AND EXISTS 
(select 1 FROM oc_product_attribute p2a0 
WHERE p2a0.product_id=p2a.product_id AND p2a0.attribute_id = 20 
AND (p2a0.text = '2х36 Вт' OR p2a0.text like '2х36 Вт:%' OR p2a0.text like '%:2х36 Вт' OR p2a0.text like '%:2х36 Вт:%')) 
AND EXISTS 
(select 1 FROM oc_product_attribute p2a1 
WHERE p2a1.product_id=p2a.product_id AND p2a1.attribute_id = 22 
AND (p2a1.text = 'накладные' OR p2a1.text like 'накладные:%' OR p2a1.text like '%:накладные' OR p2a1.text like '%:накладные:%')) 
AND EXISTS 
(select 1 FROM oc_product_attribute p2a2 
WHERE p2a2.product_id=p2a.product_id AND p2a2.attribute_id = 27 
AND (p2a2.text = '120 см' OR p2a2.text like '120 см:%' OR p2a2.text like '%:120 см' OR p2a2.text like '%:120 см:%')) </b>
AND p.status = '1' AND p2s.store_id = 0) as innertable WHERE 1 GROUP BY attribute_id, text

Rewritten version:

SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 
LEFT JOIN oc_product_to_store p2s ON (p2s.product_id=p.product_id) 
LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=p.product_id) 
LEFT JOIN oc_category_path cp ON(cp.category_id=p2c.category_id) 
LEFT JOIN oc_product_attribute_id p2a ON (p2a.product_id=p.product_id) 
<b>WHERE p2a.attribute_id IS NOT NULL 
AND EXISTS (select 1 FROM oc_product_attribute_id p2a0 WHERE p2a0.product_id=p2a.product_id AND p2a0.text_id = 67) 
AND EXISTS (select 1 FROM oc_product_attribute_id p2a1 WHERE p2a1.product_id=p2a.product_id AND p2a1.text_id = 82)
AND EXISTS (select 1 FROM oc_product_attribute_id p2a2 WHERE p2a2.product_id=p2a.product_id AND p2a2.text_id = 88) </b>
AND p.status = '1' AND p2s.store_id = 0) as innertable WHERE 1 GROUP BY attribute_id, text

EXPLAIN: prntscr.com/edx5zj
Data structure:
prntscr.com/edxyjr

Answer 1

Understood, done.

SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 
LEFT JOIN oc_product_to_store p2s ON (p2s.product_id=p.product_id) 
LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=p.product_id) 
LEFT JOIN oc_category_path cp ON(cp.category_id=p2c.category_id) 
LEFT JOIN oc_product_attribute p2a ON (p2a.product_id=p.product_id) 
WHERE p2a.attribute_id IS NOT NULL AND EXISTS 
(select 1 FROM oc_product_attribute p2a0 
WHERE p2a0.product_id=p2a.product_id AND p2a0.attribute_id = 20 
AND (p2a0.text = '2х36 Вт' OR p2a0.text like '2х36 Вт:%' OR p2a0.text like '%:2х36 Вт' OR p2a0.text like '%:2х36 Вт:%')) 
AND EXISTS (select 1 FROM oc_product_attribute p2a1 WHERE p2a1.product_id=p2a.product_id AND p2a1.attribute_id = 22 
AND (p2a1.text = 'накладные' OR p2a1.text like 'накладные:%' OR p2a1.text like '%:накладные' OR p2a1.text like '%:накладные:%')) 
AND EXISTS (select 1 FROM oc_product_attribute p2a2 WHERE p2a2.product_id=p2a.product_id AND p2a2.attribute_id = 27 
AND (p2a2.text = '120 см' OR p2a2.text like '120 см:%' OR p2a2.text like '%:120 см' OR p2a2.text like '%:120 см:%')) 
AND p.status = '1' AND p2s.store_id = 0) as innertable WHERE 1 GROUP BY attribute_id, text

SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 
LEFT JOIN oc_product_to_store p2s ON (p2s.product_id=p.product_id) 
LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=p.product_id) 
LEFT JOIN oc_category_path cp ON(cp.category_id=p2c.category_id)
LEFT JOIN oc_product_attribute_id p2a ON (p2a.product_id=p.product_id)
INNER JOIN oc_product_attribute_id p2a0 ON (p2a0.product_id=p.product_id) 
INNER JOIN oc_product_attribute_id p2a1 ON (p2a1.product_id=p.product_id) 
INNER JOIN oc_product_attribute_id p2a2 ON (p2a2.product_id=p.product_id) 
WHERE (p2a0.attribute_id = 20 AND p2a0.text like '%2х36 Вт%') 
AND  (p2a1.attribute_id = 22 AND p2a1.text like '%накладные%')
AND  (p2a2.attribute_id = 27 AND p2a2.text like '%120 см%')
) as innertable WHERE 1 GROUP BY attribute_id, text

Time 0.13 ->0.05.
When LIKE %...% was replaced with text_id the time was 0.04 :

SELECT SQL_NO_CACHE attribute_id, text FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice 
FROM oc_product p 
LEFT JOIN oc_manufacturer m ON(m.manufacturer_id=p.manufacturer_id) 
LEFT JOIN oc_product_option_value pov ON (pov.product_id=p.product_id) 
LEFT JOIN oc_product_to_store p2s ON (p2s.product_id=p.product_id) 
LEFT JOIN oc_product_to_category p2c ON (p2c.product_id=p.product_id) 
LEFT JOIN oc_category_path cp ON(cp.category_id=p2c.category_id)
INNER JOIN oc_product_attribute_id p2a0 ON (p2a0.product_id=p.product_id) 
INNER JOIN oc_product_attribute_id p2a1 ON (p2a1.product_id=p.product_id) 
INNER JOIN oc_product_attribute_id p2a2 ON (p2a2.product_id=p.product_id) 
LEFT JOIN oc_product_attribute_id p2a ON (p2a.product_id=p.product_id)
WHERE (p2a0.text_id = '67')
AND  (p2a1.text_id = '82')
AND  (p2a2.text_id = '88')
) as innertable WHERE 1 GROUP BY attribute_id, text

Moreover, the location of the line LEFT JOIN oc_product_attribute_id p2a ON (p2a.product_id=p.product_id) affects the time by 2 times. After Inner join is faster

Answer 2

All fields participating in the WHERE conditions must have an index. Ideally, this is either PK or UNIQUE. I think that your problem is somewhere in this direction, but without detailed information about your database, I can’t say anything more precisely.

Answer 3

If you want a speed request, then get rid of "nested" requests. This:
For each line, the system also has to make an additional request.
Remove unnecessary joins.
For example you have
And now find where you communicate with M in the data sample.
If you display attribute_id, text data,
then why do you need a bunch of fields in the subquery?

WHERE 1

I'm not a MySQL expert but I don't understand why you need this???
We also see the condition:

WHERE p2a.attribute_id IS NOT NULL

If p2a.attribute_id is required, then it is worth writing an INNER JOIN to the table and not torturing yourself with unnecessary and useless conditions.