How can this query be optimized?

Question

MySQL

Hey! How it is possible to optimize the given request? Indexes (primary) on those fields where join stand. There is an index (status, date_available) and (path_id, category_id). I tried to add a composite index for order by, but it does not give any speed increase. Filesort is also used in oc_category_path and order by , how can I get rid of this ?

SELECT
        `fgd`.`name` AS `gname`,
        `fgd`.`mf_tooltip` AS `tooltip`,
        `f`.`filter_group_id`,
        `f`.`filter_id`,
        `fd`.`name`
      FROM
        `oc_product` AS `p`
      INNER JOIN
        `oc_product_filter` AS `pf`
      ON
        `p`.`product_id` = `pf`.`product_id`
      INNER JOIN
        `oc_filter` AS `f`
      ON
        `pf`.`filter_id` = `f`.`filter_id`
      INNER JOIN
        `oc_filter_description` AS `fd`
      ON
        `pf`.`filter_id` = `fd`.`filter_id`
      INNER JOIN
        `oc_filter_group_description` AS `fgd`
      ON
        `f`.`filter_group_id` = `fgd`.`filter_group_id`
      
      INNER JOIN
        `oc_product_to_category` AS `p2c`
      ON
        `p2c`.`product_id` = `p`.`product_id`
    
      WHERE
        `p`.`status` = '1' AND `p`.`date_available` <= NOW() AND `p2c`.`category_id` IN(SELECT `category_id` FROM `oc_category_path` WHERE `path_id` IN(66))
GROUP BY
        `f`.`filter_group_id`, `f`.`filter_id`
      ORDER BY
        `f`.`sort_order`, `fd`.`name`

Here is the explain of the request:

Answer 1

If oc_product_to_category is a heavily loaded table, you can put the subselect in a separate query.
As an option - you can smoke on the topic of transaction isolation levels with non-blocking reading.
If the above screenshot is not from production, I have some bad news for you: explain may easily not match the local one.

Answer 2

build indexes correctly
8.2.1.17 GROUP BY Optimization
8.2.1.16 ORDER BY Optimization