how optimize prestashop category get product for random

Question

this is prestashop 1.7 version category get product query. if use random, it is very slow, how optimize it?

SELECT
    cp.id_category,
    p.*,
    product_shop.*,
    stock.out_of_stock,
    IFNULL( stock.quantity, 0 ) AS quantity,
    IFNULL( product_attribute_shop.id_product_attribute, 0 ) AS id_product_attribute,
    product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity,
    pl.`description`,
    pl.`description_short`,
    pl.`available_now`,
    pl.`available_later`,
    pl.`link_rewrite`,
    pl.`meta_description`,
    pl.`meta_keywords`,
    pl.`meta_title`,
    pl.`name`,
    image_shop.`id_image` id_image,
    il.`legend` AS legend,
    m.`name` AS manufacturer_name,
    cl.`name` AS category_default,
    DATEDIFF(
        product_shop.`date_add`,
    DATE_SUB( "2019-11-30 00:00:00", INTERVAL 7 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_shop` product_attribute_shop ON ( p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop = 1 )
    LEFT JOIN ps_stock_available stock ON ( stock.id_product = `p`.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1 AND stock.id_shop_group = 0 )
    LEFT JOIN `ps_category_lang` cl ON ( product_shop.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 11 AND cl.id_shop = 1 )
    LEFT JOIN `ps_product_lang` pl ON ( p.`id_product` = pl.`id_product` AND pl.`id_lang` = 11 AND pl.id_shop = 1 )
    LEFT JOIN `ps_image_shop` image_shop ON ( image_shop.`id_product` = p.`id_product` AND image_shop.cover = 1 AND image_shop.id_shop = 1 )
    LEFT JOIN `ps_image_lang` il ON ( image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 11 )
    LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer` 
WHERE
    product_shop.`id_shop` = 1 
    AND cp.`id_category` = 12 
    AND product_shop.`active` = 1 
    AND product_shop.`visibility` IN ( "both", "catalog" ) 
ORDER BY
    RAND() 
    LIMIT 50

Answer 1

Please provide SHOW CREATE TABLE for each table. Meanwhile, ...

Let's start by optimizing the joins.

    LEFT JOIN  `ps_product_lang` pl  ON ( p.`id_product` = pl.`id_product`
              AND  pl.`id_lang` = 11
              AND  pl.id_shop = 1   )

That needs INDEX(id_product, id_lang, id_shop) (The columns may be in any order.)

Don't use LEFT unless you really need to fetch a row from the righthand table as NULLs when it does not exist. In particular,

LEFT JOIN  `ps_product` p

is probably getting in the way of optimization.

    WHERE  product_shop.`id_shop` = 1
      AND  product_shop.`active` = 1
      AND  product_shop.`visibility` IN ( "both", "catalog" )

would probably benefit from these indexes

INDEX(id_shop, active, visibility, id_product)
INDEX(id_product, id_shop, active, visibility)

product_category needs

INDEX(id_category, id_product)   -- in this order.

In general many-to-many mapping tables need to follow the tips here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

The query has the "explode-implode" syndrome. This is where it first does a JOINs, collecting a lot of data, then throws away much of it due, in your case, to the LIMIT 10. It can probably be cured by turning the query inside-out. The general ID is to start with a derived table that gets the 10 rows desired, then reaches into the other table for the rest of the desired columns. This "reaching" need happen only 10 times, not however many the JOINs currently require.

SELECT ...
    FROM ( SELECT <<primary key columns from cp, p, and product_shop>>
               FROM cp
               JOIN p  ON ...
               JOIN product_shop  ON ...
               ORDER BY RAND()
               LIMIT 10 ) AS x
    JOIN  <<p, product_shop ON their PKs>>   -- to get p.*, product_shop.*>>
    [LEFT] JOIN  << each of the other tables>>  -- to get the other tables

You should start by testing the subquery (a "derived" table) to verify that it is noticeably faster than the original query.