Несколько Join таблицы саму на себя

 CREATE TABLE `product` (
 `product_id` int(11) NOT NULL AUTO_INCREMENT,
 `model` varchar(64) NOT NULL,
 `quantity` int(4) NOT NULL DEFAULT '0',
 `image` varchar(255) DEFAULT NULL,
 `manufacturer_id` int(11) NOT NULL,
 `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
 `date_available` date NOT NULL DEFAULT '1990-09-02',
 `viewed` int(5) NOT NULL DEFAULT '0',
 `date_added` datetime NOT NULL,
 `date_modified` datetime NOT NULL,
 PRIMARY KEY (`product_id`),
 KEY `date_modified` (`date_modified`),
 KEY `quantity_image` (`quantity`,`image`),
 KEY `model` (`model`),
 KEY `manufacturer_id` (`manufacturer_id`),
 KEY `price` (`price`)
) ENGINE=MyISAM AUTO_INCREMENT=125826 DEFAULT CHARSET=utf8

CREATE TABLE `attribute` (
 `attribute_id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(32) NOT NULL,
 `value` varchar(32) NOT NULL,
 PRIMARY KEY (`attribute_id`),
 KEY `attribute_id_name_value` (`attribute_id`,`name`,`value`)
) ENGINE=MyISAM AUTO_INCREMENT=657 DEFAULT CHARSET=utf8

CREATE TABLE `product_attribute` (
 `product_id` int(11) NOT NULL,
 `attribute_id` int(11) NOT NULL,
 PRIMARY KEY (`product_id`,`attribute_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

    SELECT p.model,
         p.product_id,
            
FROM product p
INNER JOIN product_attribute AS pa1
    ON (p.product_id=pa1.product_id)
INNER JOIN product_attribute AS pa2
    ON (p.product_id=pa2.product_id)
INNER JOIN product_attribute AS pa3
    ON (p.product_id=pa3.product_id)
INNER JOIN product_attribute AS pa4
    ON (p.product_id=pa4.product_id)
INNER JOIN product_attribute AS pa5
    ON (p.product_id=pa5.product_id)
INNER JOIN product_attribute AS pa6
    ON (p.product_id=pa6.product_id)
INNER JOIN product_attribute AS pa7
    ON (p.product_id=pa7.product_id)
INNER JOIN product_attribute AS pa8
    ON (p.product_id=pa8.product_id)
WHERE p.quantity > 0
        AND pa1.attribute_id IN (12, 23)
        AND pa2.attribute_id IN (2, 37, 42, 54, 1)
        AND pa3.attribute_id IN (4, 38)
        AND pa4.attribute_id IN (535)
        AND pa5.attribute_id IN (7, 13)
        AND pa6.attribute_id IN (418, 368, 237)
        AND pa7.attribute_id IN (8, 14, 41)
        AND pa8.attribute_id IN (24, 22, 2) AND p2c.category_id IN (15, 5) AND p.manufacturer_id IN (197, 62, 4) AND p.price BETWEEN 32317 AND 1000000 GROUP BY p.model LIMIT 0,60

SELECT model, 
       GROUP_CONCAT(product_id) product_ids -- группировка только по model -
                                            -- обязательна агрегатная функция
FROM product
GROUP BY model
HAVING SUM(attribute_id IN (12, 23) AND category_id IN (15, 5))
   AND SUM(attribute_id IN (2, 37, 42, 54, 1))
-- ...  
   AND SUM(attribute_id IN (8, 14, 41))
   AND SUM(attribute_id IN (24, 22, 2))
   AND manufacturer_id IN (197, 62, 4) 
   AND p.price BETWEEN 32317 AND 1000000
ORDER BY ?????       -- LIMIT без сортировки - это лотерея
LIMIT 0,60

Если СУБД PostgreSQL - возможно, придётся добавить ко всем суммам SUM(...) > 0.

Можно попробовать поднять условия в join'ы:

 SELECT p.model,
         p.product_id
            
FROM oc_product p
JOIN oc_product_attribute a1 ON a1.product_id=p.product_id AND a1.attribute_id IN (5)
JOIN oc_product_attribute a2 ON a2.product_id=p.product_id AND a2.attribute_id IN (6)
JOIN oc_product_attribute a3 ON a3.product_id=p.product_id AND a3.attribute_id IN (7)
WHERE p.quantity > 0

Или перенести join'ы в подзапросы фильтра:

SELECT p.model,
         p.product_id
FROM oc_product p
WHERE p.quantity > 0
  AND EXISTS(SELECT 1 FROM oc_product_attribute WHERE 
    product_id=p.product_id and attribute_id IN (5))
  AND EXISTS(SELECT 1 FROM oc_product_attribute WHERE 
    product_id=p.product_id and attribute_id IN (6))
  AND EXISTS(SELECT 1 FROM oc_product_attribute WHERE 
    product_id=p.product_id and attribute_id IN (7));

dbfiddle.uk

Также я добавил в фиддл пару запросов, из которых можно понять, как работает решение от @Akina:

SELECT p.product_id, MIN(p.model) model, GROUP_CONCAT(attr.attribute_id)
FROM oc_product p 
INNER JOIN oc_product_attribute attr ON attr.product_id = p.product_id
WHERE p.quantity > 0
GROUP BY p.product_id 
HAVING SUM(attr.attribute_id IN (5)) > 0
  AND  SUM(attr.attribute_id IN (6)) > 0
  AND  SUM(attr.attribute_id IN (7)) > 0

По итогам получилось 3 вида запросов:

Запрос с множеством JOIN (1)

SELECT p.model, p.product_id
FROM oc_product p
LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id)
INNER JOIN oc_product_attribute as pa1 ON (p.product_id=pa1.product_id)
......
INNER JOIN oc_product_attribute as pa8 ON (p.product_id=pa8.product_id)
WHERE p.quantity > 0 AND 
pa1.attribute_id IN (10) 
.....
AND pa8.attribute_id IN (632,103) AND p2c.category_id IN (46, 81, 1)
AND p.manufacturer_id IN (4)
GROUP BY p.model, p.product_id

Запрос, который предложил @Akina (2)

SELECT model, product_id
FROM oc_product p 
INNER JOIN oc_product_to_category p2c USING (product_id)
INNER JOIN oc_product_attribute pa USING (product_id)
   Where p.quantity > 0 AND p.manufacturer_id IN (4) AND p2c.category_id IN (1, 46, 81)
   GROUP BY model, product_id
HAVING SUM(pa.attribute_id IN (2, 33))
....
AND SUM(pa.attribute_id IN (632, 103))

И третий запрос от пользователя @vp_arth (3)

SELECT SQL_NO_CACHE  p.model,
         p.product_id
FROM oc_product p
INNER JOIN oc_product_to_category p2c USING (product_id)
INNER JOIN oc_product_attribute pa USING (product_id)
   Where p.quantity > 0 AND p.manufacturer_id IN (4) AND p2c.category_id IN (1, 46, 81)
AND EXISTS(SELECT 1 FROM oc_product_attribute WHERE 
    product_id=p.product_id and attribute_id IN (2, 33))
.....
AND EXISTS(SELECT 1 FROM oc_product_attribute WHERE 
    product_id=p.product_id and attribute_id IN (4, 38))
GROUP BY p.model,
         p.product_id

По итогам тестов пришел к следующим выводам:

1. При работе с количеством атрибутов от 1 до 7

• Запрос (1) 0.53 сек. - Время выполнения варьируется от от 0.1 до 0.5 сек в зависимости от кол-ва типов атрибутов.
• Запрос (2) 0.9647 сек. Время выполнения практически одинаковое
• Запрос (3) 0.6429 сек - Время выполнения практически одинаковое

2. При работе с количеством атрибутов от 7 до 12

• Запрос (1) > 2 сек
• Запрос (2) 0.9815 сек - Практически не изменяется
• Запрос (3) 0.6729 сек - Время выполнения практически одинаковое

Но вопрос остается - неужели нет какого-то другого средства для оптимизации данного вида запроса?