Вот :)

не понимаю как это на 5,6 может летать.... все записи всё равно придётся извлечь из этой таблицы, сагрегировать с cscart_discussion_rating, посчитать рейт...
тут в принципе летать не может....

не может это ни на 5.6 ни на 8, ни на оракле, ни на простгре ни на монге

у товара надо денормализовать average_rating тогда будет быстро работать

а это EXPLAIN данного запроса?

Да

можно DDL cscart_categories

Вот

А, не то

Ща

CREATE TABLE cscart_categories (
 category_id mediumint unsigned NOT NULL AUTO_INCREMENT,
 parent_id mediumint unsigned NOT NULL DEFAULT '0',
 id_path varchar(255) NOT NULL DEFAULT '',
 level int unsigned NOT NULL DEFAULT '1',
 company_id int unsigned NOT NULL DEFAULT '0',
 usergroup_ids varchar(255) NOT NULL DEFAULT '0',
 status char(1) NOT NULL DEFAULT 'A',
 product_count mediumint unsigned NOT NULL DEFAULT '0',
 position smallint unsigned NOT NULL DEFAULT '0',
 timestamp int unsigned NOT NULL DEFAULT '0',
 is_op char(1) NOT NULL DEFAULT 'N',
 localization varchar(255) NOT NULL DEFAULT '',
 age_verification char(1) NOT NULL DEFAULT 'N',
 age_limit tinyint NOT NULL DEFAULT '0',
 parent_age_verification char(1) NOT NULL DEFAULT 'N',
 parent_age_limit tinyint NOT NULL DEFAULT '0',
 selected_views text,
 default_view varchar(50) NOT NULL DEFAULT '',
 product_details_view varchar(50) NOT NULL DEFAULT '',
 product_columns tinyint unsigned NOT NULL DEFAULT '0',
 is_trash char(1) NOT NULL DEFAULT 'N',
 yml2_market_category varchar(255) NOT NULL,
 yml2_offer_type varchar(32) NOT NULL DEFAULT '',
 yml2_type_prefix_select varchar(32) NOT NULL DEFAULT '',
 yml2_model_select varchar(32) NOT NULL DEFAULT '',
 yml2_type_prefix varchar(64) NOT NULL DEFAULT '',
 yml2_model varchar(64) NOT NULL DEFAULT '',
 external_id varchar(128) NOT NULL DEFAULT '',
 ab__lc_catalog_image_control char(5) NOT NULL DEFAULT 'none',
 ab__lc_landing char(1) NOT NULL DEFAULT 'N',
 ab__lc_subsubcategories int NOT NULL DEFAULT '0',
 ab__lc_menu_id int NOT NULL DEFAULT '0',
 ab__lc_how_to_use_menu char(1) NOT NULL DEFAULT 'N',
 ab__lc_inherit_control char(1) NOT NULL DEFAULT 'N',
 delivery_cat_id int NOT NULL DEFAULT '0',
 PRIMARY KEY (category_id),
 UNIQUE KEY category_id_UNIQUE (category_id),
 KEY c_status (usergroup_ids,status,parent_id),
 KEY position (position),
 KEY parent (parent_id),
 KEY id_path (id_path),
 KEY localization (localization),
 KEY age_verification (age_verification,age_limit),
 KEY parent_age_verification (parent_age_verification,parent_age_limit),
 KEY idx_fshare (category_id,company_id,usergroup_ids,status) USING BTREE,
 KEY p_category_id (category_id,usergroup_ids,status) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6909 DEFAULT CHARSET=utf8

Попробуйте
1. сделать индекс
CREATE INDEX idx_fshare2 (company_id,category_id,usergroup_ids,status)
2. выполнить запрос

SELECT products.product_id
, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) as product
, products.product_type
, products.parent_product_id
, cscart_discussion.type AS discussion_type
, cscart_discussion.thread_id AS discussion_thread_id
, AVG(cscart_discussion_rating.rating_value) AS average_rating
FROM cscart_products as products
LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'ru'
INNER JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1
INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id
INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id
 AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids))
 AND cscart_categories.status IN ('A', 'H')
LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id
 AND shared_descr.company_id = 1
 AND shared_descr.lang_code = 'ru'
LEFT JOIN cscart_discussion ON cscart_discussion.object_id = products.product_id
 AND cscart_discussion.object_type = 'P'
LEFT JOIN cscart_discussion_posts ON cscart_discussion_posts.thread_id = cscart_discussion.thread_id
 AND cscart_discussion_posts.status = 'A'
LEFT JOIN cscart_discussion_rating ON cscart_discussion.thread_id = cscart_discussion_rating.thread_id
 AND cscart_discussion_rating.post_id = cscart_discussion_posts.post_id
 AND cscart_discussion_rating.rating_value != 0
WHERE 1
 AND cscart_categories.company_id = 1
 AND (products.usergroup_ids = ''
  OR FIND_IN_SET(0, products.usergroup_ids)
  OR FIND_IN_SET(1, products.usergroup_ids)
 )
 AND products.status IN ('A')
 AND prices.usergroup_id IN (0, 0, 1)
 AND products.parent_product_id = 0
GROUP BY products.product_id
, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product)
, products.product_type
, products.parent_product_id
, cscart_discussion.type
, cscart_discussion.thread_id
HAVING average_rating > 0
ORDER BY average_rating desc, products.product_id ASC
LIMIT 0, 10;

3. EXPLAIN этого запроса показать

4. EXPLAIN ANALYZE этого запроса показать

ещё, если на 5,6 работает действительно быстро, покажите оттуда EXPLAIN

Ещё момент, вам совершенно не нужны индексы в таблице cscart_categories
- category_id_UNIQUE (category_id)
- idx_fshare (category_id,company_id,usergroup_ids,status)
- p_category_id (category_id,usergroup_ids,status)
их можно смело дропать
1. category_id_UNIQUE не нужен т.к. есть PRIMARY KEY (category_id)
2. idx_fshare и p_category_id, тоже Т.к. есть PRIMARY KEY (category_id), тут мы получаем конечно Using index, но для первичного ключа это бессмысленно, т.к. данные в Innodb и так кластеризованы по первичному ключу

Сделал. Вот EXPLAIN

-> Limit: 10 row(s)  (actual time=55446.402..55446.402 rows=0 loops=1)
-> Sort: average_rating DESC, <temporary>.product_id  (actual time=55446.389..55446.389 rows=0 loops=1)
-> Filter: (average_rating > 0)  (actual time=55446.334..55446.334 rows=0 loops=1)
-> Table scan on <temporary>  (actual time=0.009..627.114 rows=182728 loops=1)
-> Aggregate using temporary table  (actual time=53235.711..54856.372 rows=182728 loops=1)
-> Nested loop left join  (cost=25754.40 rows=4435) (actual time=0.488..49943.123 rows=182728 loops=1)
-> Nested loop left join  (cost=20875.82 rows=4435) (actual time=0.455..44926.075 rows=182728 loops=1)
-> Nested loop left join  (cost=19919.92 rows=2047) (actual time=0.421..40131.921 rows=182728 loops=1)-> Nested loop left join  (cost=19203.48 rows=2047) (actual time=0.381..32670.627 rows=182728 loops=1)
-> Nested loop left join  (cost=18487.05 rows=2047) (actual time=0.348..26895.051 rows=182728 loops=1)
-> Nested loop inner join  (cost=17770.61 rows=2047) (actual time=0.313..20961.933 rows=182728 loops=1)
-> Nested loop inner join  (cost=14904.87 rows=8188) (actual time=0.261..11676.180 rows=182728 loops=1)
-> Nested loop inner join  (cost=3442.03 rows=32751) (actual time=0.216..1888.611 rows=182728 loops=1)
-> Filter: (((cscart_categories.usergroup_ids = \'\') or (0 <> find_in_set(0,cscart_categories.usergroup_ids)) or (0 <> find_in_set(1,cscart_categories.usergroup_ids))) and (cscart_categories.status in (\'A\',\'H\')))  (cost=94.13 rows=244) (actual time=0.100..18.911 rows=1218 loops=1)
-> Index lookup on cscart_categories using idx_fshare2 (company_id=1)  (cost=94.13 rows=1218) (actual time=0.081..7.659 rows=1218 loops=1)
-> Index lookup on products_categories using PRIMARY (category_id=cscart_categories.category_id)  (cost=0.35 rows=134) (actual time=0.032..0.721 rows=150 loops=1218)
-> Filter: ((prices.lower_limit = 1) and (prices.usergroup_id in (0,0,1)))  (cost=0.25 rows=0) (actual time=0.028..0.040 rows=1 loops=182728)
-> Index lookup on prices using usergroup (product_id=products_categories.product_id)  (cost=0.25 rows=1) (actual time=0.016..0.023 rows=1 loops=182728)
-> Filter: ((products.parent_product_id = 0) and ((products.usergroup_ids = \'\') or (0 <> find_in_set(0,products.usergroup_ids)) or (0 <> find_in_set(1,products.usergroup_ids))) and (products.status = \'A\'))  (cost=0.25 rows=0) (actual time=0.030..0.037 rows=1 loops=182728)
-> Single-row index lookup on products using PRIMARY (product_id=products_categories.product_id)  (cost=0.25 rows=1) (actual time=0.016..0.018 rows=1 loops=182728)
-> Single-row index lookup on shared_descr using PRIMARY (product_id=products_categories.product_id, lang_code=\'ru\', company_id=1)  (cost=0.25 rows=1) (actual time=0.016..0.019 rows=1 loops=182728)
-> Single-row index lookup on descr1 using PRIMARY (product_id=products_categories.product_id, lang_code=\'ru\')  (cost=0.25 rows=1) (actual time=0.016..0.018 rows=1 loops=182728)
-> Index lookup on cscart_discussion using object_id (object_id=products_categories.product_id, object_type=\'P\')  (cost=0.25 rows=1) (actual time=0.019..0.027 rows=1 loops=182728)
-> Index lookup on cscart_discussion_posts using thread_id_2 (thread_id=cscart_discussion.thread_id, status=\'A\')  (cost=0.25 rows=2) (actual time=0.015..0.015 rows=0 loops=182728)
-> Filter: ((cscart_discussion_rating.thread_id = cscart_discussion.thread_id) and (cscart_discussion_rating.rating_value <> 0))  (cost=1.00 rows=1) (actual time=0.016..0.016 rows=0 loops=182728)
-> Single-row index lookup on cscart_discussion_rating using PRIMARY (post_id=cscart_discussion_posts.post_id)  (cost=1.00 rows=1) (actual time=0.006..0.006 rows=0 loops=182728)

Это EXPLAIN ANALYZE

Есть магазин, не мой, там почти всё из коробки и там работает быстро. Здесь не публикуется ссылка

а EXPLAIN то можно этого же запроса на 5,6