Глобально ситуация следующая:
Я установил CMS из коробки. запустил на тестовых данных и всё было хорошо. Сделал импорт текущего боевого сайта (Другая CMS - самописка). Там примерно 200 твс товаров. И вот тут всё просело... Сначала было открытие карточки товара 5-7 минут. Потом я настроил индексы, перевел таблицы из MyIsam в InnoDB и вот сейчас один запрос выполняется уже 7-10 секунд, а страница товара открывается около 40 секунд, но иногда до 1 минуты дотягивает

Вот я не понимаю, что еще нужно подкрутить

Это 8-я версия MySQL и гугл особо не помогает =(

так как она типа супер оптимизирована для многопроцессорных машин, у меня 4 ядра и что-то не сильно всё круто

Хотя, есть машинка с 1-им ядром и там стоит 5.6 варсия MySQL и там нормально крутятся таблички с 1М записями

выкинь SQL_CALC_FOUND_ROWS

из запроса

Пробовал

не сильно роляет

и?

ломается логика ядра.

Вот запрос сократил до 1-го JOIN'a что бы понять, что не так

это как я понял нужно что бы сделать красиво пагинацию

Тут проблема именно в настройках

buffer pool size выкрутил?

Там еще в карточке отображается количество товаров в этой же категории

Да

да это быстро считается

но надо написать правильно, а не через SQL_CALC_FOUND_ROWS

поставь индекс uniq