Magento 2 slow category page, search_tmp tables

Question

I'm trying to debug a slow Magento 2 category page, containing more than 10000 products. The following query keeps coming back, sometimes even blocking other queries and slowing down the whole site.

SELECT
    1 AS `status`,
    `e`.`entity_id`,
    `e`.`attribute_set_id`,
    `e`.`type_id`,
    `e`.`created_at`,
    `e`.`updated_at`,
    `e`.`sku`,
    `cat_index`.`position` AS `cat_index_position`,
    `e`.`name`,
    `e`.`short_description`,
    `e`.`price`,
    `e`.`special_price`,
    `e`.`special_from_date`,
    `e`.`special_to_date`,
    `e`.`manufacturer`,
    `e`.`manufacturer_value`,
    `e`.`image`,
    `e`.`small_image`,
    `e`.`thumbnail`,
    `e`.`news_from_date`,
    `e`.`news_to_date`,
    `e`.`required_options`,
    `e`.`image_label`,
    `e`.`small_image_label`,
    `e`.`thumbnail_label`,
    `e`.`url_key`,
    `e`.`msrp`,
    `e`.`msrp_display_actual_price_type`,
    `e`.`price_type`,
    `e`.`weight_type`,
    `e`.`price_view`,
    `e`.`links_purchased_separately`,
    `e`.`links_exist`,
    `e`.`swatch_image`,
    `e`.`tax_class_id`,
    `e`.`delivery`,
    `price_index`.`price`,
    `price_index`.`tax_class_id`,
    `price_index`.`final_price`,
    IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`,
    `price_index`.`min_price`,
    `price_index`.`max_price`,
    `price_index`.`tier_price`,
    `stock_status`.`stock_status` AS `is_salable`,
    (   SELECT
            SUM(order_item.qty_ordered)
        FROM
            sales_order_item AS order_item
        WHERE
            order_item.product_id = e.entity_id
        AND order_item.created_at > '2017-08-24'
        AND order_item.store_id = 1
    ) AS `bestsellers`,
    `stock_status_index`.`stock_status` AS `is_salable`
FROM
    `catalog_product_flat_1` AS `e`
INNER JOIN
    `catalog_category_product_index` AS `cat_index`
ON
    cat_index.product_id=e.entity_id
AND cat_index.store_id=1
AND cat_index.visibility IN(2, 4)
AND cat_index.category_id='14'
INNER JOIN
    `catalog_product_index_price` AS `price_index`
ON
    price_index.entity_id = e.entity_id
AND price_index.website_id = '1'
AND price_index.customer_group_id = '1'
INNER JOIN
    `search_tmp_59ee2169992f00_12966690` AS `search_result`
ON
    e.entity_id = search_result.entity_id
LEFT JOIN
    `cataloginventory_stock_status` AS `stock_status`
ON
    e.entity_id = stock_status.product_id
AND stock_status.website_id=0
LEFT JOIN
    `cataloginventory_stock_status` AS `stock_status_index`
ON
    e.entity_id = stock_status_index.product_id
AND stock_status_index.website_id = 0
AND stock_status_index.stock_id = 1
ORDER BY
    `stock_status_index`.`stock_status` desc,
    IF(IFNULL(`small_image`, "no_selection")="no_selection", 1, 0) asc,
    `bestsellers` desc
LIMIT 30

This seems to be a Magento 2.1.x bug (more info here: magento2 search_tmp join leads to empty results in category listing). But the solution given there didn't work for me.

Is anyone familiar with this Magento 2 bug? What can we do to solve this problem?

Answer 1

The issue was caused by the Amasty Sorting module for Magento 2. The older version of the module that we used didn't used separate indexes for the bestseller sorting, which resulted in mysql locks on bigger categories (> 5000 products).

We've updated the module to the latest version (the latest version includes seperate indexes for sorting) and this solved the problem for us.