How to beat slow SQL query with multiple JOINs?

Question

MySQL

The request has been running for more than 2 minutes. Initially, instead of text_id, it was LIKE '%text%'. I changed it to ID, but the problem persisted.
If we remove a pair of JOINs such as "INNER JOIN oc_product_attribute_id p2a7", then the time drops tenfold.
What happens, why start with a certain number of JOIN brakes?
How are such situations treated?

SELECT count(*) as total, attribute_id, text  
FROM (
SELECT DISTINCT p.product_id, p2a.attribute_id, p2a.text, p.price as realprice  
FROM oc_product p 
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)) 
INNER JOIN oc_product_attribute_id p2a3 ON ((p2a3.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a4 ON ((p2a4.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a5 ON ((p2a5.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a6 ON ((p2a6.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a7 ON ((p2a7.product_id=p.product_id)) 
INNER JOIN oc_product_attribute_id p2a ON (p2a.product_id=p.product_id) 
WHERE 1 
AND (p2a0.text_id = '126') 
AND (p2a1.text_id = '63') 
AND (p2a2.text_id = '120') 
AND (p2a3.text_id = '121') 
AND (p2a4.text_id = '82') 
AND (p2a5.text_id = '15') 
AND (p2a6.text_id = '86') 
AND (p2a7.text_id = '70') 
AND p.status = '1') as innertable 
WHERE 1  GROUP BY attribute_id, text;

If you leave the selection with all JOINs only by oc_product_attribute_id, then the speed is about 0.4 seconds, which is still not fast.
oc_product - 117k records
oc_product_attribute_id - 21k records

Answer 1

First, for count(*) as total, count at least the ID.
And I would already in the code (as I understand it, this is an online store) would have developed an analogue of "faceted search". As I understand it, in a nested (large) query, you search for a product by attributes.
And finally - try without INNER JOIN - on matrix multiplication and check it yourself in WHERE (sometimes it's faster than JOIN)