@myspaceeeeee кстати это обозначает, что связи в каждом подзапроса 1:0..1, так что это МОЖНО перепилить на JOIN-ы, а если можно, значит и нужно.
Только не пытайся одну таблицу привязать сложным JOIN-ом, привяжи две или три, потом в выражении CASE-ом возми из трёх таблиц нужные значения по условию

ваш совет делать всегда джоины вместо подзапросов, или как?

можно так
left join product_to_categories as ptc ON(ptc.product_id = m.id AND ptc.category_id = 15920173)
можно так
WHERE m.id in (select product_id from product_to_categories where category_id = 15920173)

Нет, конечно, но если можно делать join, то так и нужно делать, это проще понимать и разрабатывать

Это 2 совершенно разных по смыслу запроса

согласен, можно удалить даже такой дурной пример

спасибо, большое, проблема пока-что решилась как-раз через джоины, скорость в 10раз выросла

Ну тут и JOINны не особо важны, важно было расписать корреляцию по нормальным , оптимизируемым условиям.

покаж может?

еще пишу

select distinct count(m.id)
FROM products as m
        left join products as v ON (m.id = v.main_id AND v.type = 3)
        left join product_specials ps ON (ps.product_id = m.id)
        left join product_specials vps ON (vps.product_id = v.id)
        left join product_discounts pd ON (pd.product_id = m.id)
        left join product_discounts vpd ON (vpd.product_id = v.id)
WHERE m.id in (select product_id from product_to_categories where category_id = 15920173)
 AND m.status = 1
 and m.date_available <= '2020-11-23 13:43:28'

product_discounts на этой таблице когда два джоина вместе case when 1.5c секономил

ЧТо этио за бредняк ?
select distinct count(m.id)

да это тест

там совсем другое нужно тянуть)

count() и так одна запись всегда, зачем distinct ?

конечно, count и не нужен

дальше мне просто условия подставлять и таблицы соотвествующие?

Ну да. Только учти, пока ты окончательный запрос не напишешь, временные замеры не очень полезны...

уже скоро будет