how to improve this query performance

Question

I have an application that's running this following query

SELECT SQL_NO_CACHE 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.`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 200
                                                               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 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` = 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` = 26655
  AND product_shop.`active` = 1
  AND product_shop.`visibility` IN ("both", "catalog")
GROUP BY product_shop.id_product
ORDER BY cp.`position` asc
LIMIT 0,12

And I would like to improve it's performance however I don't know where to start.

From what I see in all joined tables there are indexes for the primary keys but the problem comes from the creation of a temporary table which takes about 3seconds to copy data to.

Any ideas how I can approach this problem and improve performance?

The hardware specs are

Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz
64GB RAM
1TB Pci-E NVME DISK

I'm adding the table structure here as per suggestions

ps_category_product

create table ps_category_product
(
    id_category int unsigned not null,
    id_product int unsigned not null,
    position int unsigned default 0 not null,
    primary key (id_category, id_product)
);

create index id_product
    on ps_category_product (id_product);

ps_product

create table ps_product
(
    id_product int unsigned auto_increment
        primary key,
    id_supplier int unsigned null,
    id_manufacturer int unsigned null,
    id_category_default int unsigned null,
    id_shop_default int unsigned default 1 not null,
    id_tax_rules_group int(11) unsigned not null,
    on_sale tinyint(1) unsigned default 0 not null,
    online_only tinyint(1) unsigned default 0 not null,
    ean13 varchar(13) null,
    upc varchar(12) null,
    ecotax decimal(17,6) default 0.000000 not null,
    quantity int(10) default 0 not null,
    minimal_quantity int unsigned default 1 not null,
    price decimal(20,6) default 0.000000 not null,
    wholesale_price decimal(20,6) default 0.000000 not null,
    unity varchar(255) null,
    unit_price_ratio decimal(20,6) default 0.000000 not null,
    additional_shipping_cost decimal(20,2) default 0.00 not null,
    reference varchar(32) null,
    supplier_reference varchar(32) null,
    location varchar(64) null,
    width decimal(20,6) default 0.000000 not null,
    height decimal(20,6) default 0.000000 not null,
    depth decimal(20,6) default 0.000000 not null,
    weight decimal(20,6) default 0.000000 not null,
    out_of_stock int unsigned default 2 not null,
    quantity_discount tinyint(1) default 0 null,
    customizable tinyint(2) default 0 not null,
    uploadable_files tinyint default 0 not null,
    text_fields tinyint default 0 not null,
    active tinyint(1) unsigned default 0 not null,
    redirect_type enum('', '404', '301', '302') default '' not null,
    id_product_redirected int unsigned default 0 not null,
    available_for_order tinyint(1) default 1 not null,
    available_date date not null,
    `condition` enum('new', 'used', 'refurbished') default 'new' not null,
    show_price tinyint(1) default 1 not null,
    indexed tinyint(1) default 0 not null,
    visibility enum('both', 'catalog', 'search', 'none') default 'both' not null,
    cache_is_pack tinyint(1) default 0 not null,
    cache_has_attachments tinyint(1) default 0 not null,
    is_virtual tinyint(1) default 0 not null,
    cache_default_attribute int unsigned null,
    date_add datetime not null,
    date_upd datetime not null,
    advanced_stock_management tinyint(1) default 0 not null,
    netopricew tinyint default 0 null,
    netopricew01 tinyint default 0 null,
    netopricer tinyint default 0 null
);

create index date_add
    on ps_product (date_add);

create index id_category_default
    on ps_product (id_category_default);

create index indexed
    on ps_product (indexed);

create index product_manufacturer
    on ps_product (id_manufacturer);

create index product_supplier
    on ps_product (id_supplier);

product_shop

create table ps_product_shop
(
    id_product int unsigned not null,
    id_shop int unsigned not null,
    id_category_default int unsigned null,
    id_tax_rules_group int(11) unsigned not null,
    on_sale tinyint(1) unsigned default 0 not null,
    online_only tinyint(1) unsigned default 0 not null,
    ecotax decimal(17,6) default 0.000000 not null,
    minimal_quantity int unsigned default 1 not null,
    price decimal(20,6) default 0.000000 not null,
    wholesale_price decimal(20,6) default 0.000000 not null,
    unity varchar(255) null,
    unit_price_ratio decimal(20,6) default 0.000000 not null,
    additional_shipping_cost decimal(20,2) default 0.00 not null,
    customizable tinyint(2) default 0 not null,
    uploadable_files tinyint default 0 not null,
    text_fields tinyint default 0 not null,
    active tinyint(1) unsigned default 0 not null,
    redirect_type enum('', '404', '301', '302') default '' not null,
    id_product_redirected int unsigned default 0 not null,
    available_for_order tinyint(1) default 1 not null,
    available_date date not null,
    `condition` enum('new', 'used', 'refurbished') default 'new' not null,
    show_price tinyint(1) default 1 not null,
    indexed tinyint(1) default 0 not null,
    visibility enum('both', 'catalog', 'search', 'none') default 'both' not null,
    cache_default_attribute int unsigned null,
    advanced_stock_management tinyint(1) default 0 not null,
    date_add datetime not null,
    date_upd datetime not null,
    primary key (id_product, id_shop)
);


create index date_add
    on ps_product_shop (date_add, active, visibility);

create index id_category_default
    on ps_product_shop (id_category_default);

ps_product_attribute

create table ps_product_attribute
(
    id_product_attribute int unsigned auto_increment
        primary key,
    id_product int unsigned not null,
    reference varchar(32) null,
    supplier_reference varchar(32) null,
    location varchar(64) null,
    ean13 varchar(13) null,
    upc varchar(12) null,
    wholesale_price decimal(20,6) default 0.000000 not null,
    price decimal(20,6) default 0.000000 not null,
    ecotax decimal(17,6) default 0.000000 not null,
    quantity int(10) default 0 not null,
    weight decimal(20,6) default 0.000000 not null,
    unit_price_impact decimal(17,2) default 0.00 not null,
    default_on tinyint(1) unsigned default 0 not null,
    minimal_quantity int unsigned default 1 not null,
    available_date date null,
    active tinyint default 1 null,
    upcoming_info varchar(80) null,
    upcoming_info_upd date null,
    by_order tinyint default 0 null
);

create index id_product_id_product_attribute
    on ps_product_attribute (id_product_attribute, id_product);

create index product_attribute_product
    on ps_product_attribute (id_product);

create index product_default
    on ps_product_attribute (id_product, default_on);

create index reference
    on ps_product_attribute (reference);

create index supplier_reference
    on ps_product_attribute (supplier_reference);

requested configuration info

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb_io_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 2000  |
| innodb_io_capacity_max | 4000  |
+------------------------+-------+
2 rows in set (0.00 sec)

Answer 1

Smells like "explode-implode". This happens when you do JOINs, then collapse back via GROUP BY.

The usual cure is to first devise the minimal query to find the ids (or whatever the PRIMARY KEY is, apparently id_product) for the main table (product_shop):

SELECT id_product
    FROM product_shop
    JOIN ... -- only if you need to filter on some other tables
    ORDER BY .. LIMIT .. -- only if you are filtering this way, too

Then use that as a "derived" table in the main query:

SELECT ...   -- everything you need
    FROM ( the-derived-table-above ) AS x
    JOIN product_shop AS p USING(product_id)  -- since you need other columns from it
    JOIN ...  -- whatever else you need
    WHERE ...
    ORDER BY ... -- you cannot trust the order by of the derived table
    LIMIT ...  -- probably not needed, if the derived table suffices

In some cases (I am not sure about your case), the fact that you can whittle the number of rows down to 12 in the derived table does two potentially big things for performance: only 12 sets of JOINs are needed, and the final ORDER BY .. LIMIT (if needed) is trivially fast since there are only 12 rows.

However, there is a risk that the JOINs don't really let you move the LIMIT into the derived table -- The JOINs may lead to more than or less than 12 rows. INNER JOIN may result in fewer or more rows; LEFT JOIN cannot result in fewer rows. (You have both, and I don't know your data.)

(I don't think I have ever seen a useful Profile -- notice how 99.9% of the time is spent in the un-useful "copying to tmp table".)

Other issues...

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)
WHERE product_shop.`id_shop` = 1
  AND cp.`id_category` = 26655
  AND product_shop.`active` = 1
  AND product_shop.`visibility` IN ("both", "catalog")
GROUP BY product_shop.id_product
ORDER BY cp.`position` asc
LIMIT 0,12

Since there are WHERE clauses on product_shop, it is not really a LEFT join. Please remove LEFT so that neither I nor the Optimizer has to stand on our heads to discover this.

-- for `product_shop`:
INDEX(id_shop, active,   -- in either order
      visibility,        -- next; is IN
      id_product)        -- (not sure if this will help)
-- for `ps_category_product`:
INDEX(id_category)   -- unless it is `PRIMARY KEY(id_category)`

Sorry, I am not sure about these. I really need to see SHOW CREATE TABLE to know what the PRIMARY KEYs are. The Optimizer chose to start with ps_category_product; I cannot tell whether that was because it is better or because of a missing index. It seems that either ps_category_product or product_shop could be hit first. I need more info to advise on index changes.

Answer 2

The first two lines of your SELECT query are requesting x.* all columns from the first two tables. This use of * is causing about 80 columns (x average row length) of data to be pushed into world of 'Copying to Tmp Table'. Is it possible - naming the columns you really need (for JOIN and WHERE, processing) will reduce the 3.3 seconds reported by MySQL profiler? First thing to look for in any slow query is * (asterisk) and consider using least amount of data to complete the query (processing required).

Could you

SET @sdateless200d=DATE_SUB(NOW(), INTERVAL 200 DAY);

before the SELECT query and change your DATEDIFF line to use it as a constant with something lilke

DATEDIFF(product_shop,`date_add`, @sdateless200d) > 0 AS new, AS orderprice

to avoid NON-DETERMINISTIC and avoid system calls for NOW()?

Thanks for the innodb_io_capacity% results. It is likely the rated capacity is much higher than your current 2000. Take your vendors NVME rated capacity number * .4 (to be conservative) and use for innodb_io_capacity in your configuration. Keep innodb_io_capacity_max OUT of config and start up will multiply by 2 for the max.