How to optimize a query to MySQL (use explain, lots of pictures)?

Question

MySQL

I study the topic. There is such a monster in the slow requests log:

https://dl.dropboxusercontent.com/u/19954007/asks/...

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) 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`) 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` = 218 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;

here is what explain gives:
https://dl.dropboxusercontent.com/u/19954007/asks/... it
seems that indexes are everywhere
But if, for example, we take one field used in JOIN - image_shop.id_image and look at the table with it

https: //dl.dropboxusercontent.com/u/19954007/asks/...
you can see that it has no index. That is, there is a composite index.
tell me, if I create an additional index personally for mage_shop.id_image, will this speed up the process?
PS one more incomprehensible thing: when profiling a query, it is clear that most of the time is spent copying to a temporary table

https://dl.dropboxusercontent.com/u/19954007/asks/...
so perhaps the main problems do not concern the optimization of the query itself but rather MYSQL optimizations?
as always, any constructive thoughts welcome!
Thanks

Answer 1

Less than a second with such a request is somehow like even nothing.
Optimize -> temporary tables on SSD disk or tmpfs ram

Answer 2

you have a limit without ORDER BY