How to increase the speed of SQL query execution?

Question

MySQL

there is a creepy request:

SELECT
  p.*,
  product_shop.*,
  stock.out_of_stock,
  IFNULL(stock.quantity, 0)                                               AS quantity,
  MAX(product_attribute_shop.id_product_attribute)                        AS id_product_attribute,
  product_attribute_shop.minimal_quantity                                 AS product_attribute_minimal_quantity,
  pl.`description`,
  pl.`description_short`,
  pl.`available_now`,
  pl.video,
  pl.`available_later`,
  pl.`link_rewrite`,
  pl.`meta_description`,
  pl.`meta_keywords`,
  pl.`meta_title`,
  pl.`name`,
  MAX(image_shop.`id_image`)                                              AS id_image,
  il.`legend`,
  m.`name`                                                                AS manufacturer_name,
  cl.`name`                                                               AS category_default,
  DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 75 DAY)) > 0 AS new,
  product_shop.price                                                      AS orderprice

FROM `ps_category_product` cp LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product`
  INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
  LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product`)
  LEFT JOIN ps_product_attribute_shop product_attribute_shop
    ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND
        product_attribute_shop.`default_on` = 1)
  LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(
      `product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1)
  LEFT JOIN `ps_category_lang` cl
    ON (product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 1 AND cl.id_shop = 1)
  LEFT JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 1 AND pl.id_shop = 1)
  LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`)
  LEFT JOIN ps_image_shop image_shop
    ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover = 1)
  LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1)
  LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = 1 AND cp.`id_category` = 25 AND product_shop.`active` = 1 AND
      product_shop.`visibility` IN ("both", "catalog")
GROUP BY product_shop.id_product
ORDER BY `sale` DESC
LIMIT 0, 100;

execution plan :

here is a link to a big picture : https://dl.dropboxusercontent.com/u/19954007/asks/... you
can see that the most "heavy" one is the first one (Using temporary; Using filesort). But PRIMARY is already used there.
Is there any way to speed things up. Now the execution time is 0.9253 sec.
where to dig.
Thanks
PS The table structure is here - https://dl.dropboxusercontent.com/u/19954007/asks/...

Answer 1

Dig here dev.mysql.com/doc/refman/5.7/en/explain.html
add index on
ps_category_product.id_category
Add index on
ps_product_shop.`active`
ps_product_shop.`visibility`
then remove and compare with index on (
product_shop.`id_shop ` = 1 AND cp.`id_category` = 25 AND product_shop.`active` = 1 AND
product_shop.`visibility` IN ("both", "catalog"))

Answer 2

Find a way to add a condition to the WHERE that limits the product_shop.id_product set to the one currently in LIMIT.
Those. this request is filled with lines
ORDER BY `sale` DESC
LIMIT 0, 100;
over them and steam.