Re: Mysql sub query optimization

[Rick James]

What about oc_product_to_store ? Or is it a VIEW?

The dependent subquery is hampered by lack of index to help with
AND p2c.category_id = c.category_id
So, it started by collecting all the data:
2 DEPENDENT SUBQUERY p2s index PRIMARY PRIMARY 8 NULL 1111 Using where; Using index
and then filtering down to what is needed.

Perhaps adding this to p2c would speed it up:
INDEX(category_id, product_id)

I suspect the DERIVED form is also missing a useful index.