0

I have two tables on Mysql 8 Server. One of is products (InnoDB) other is product_filter (InnoDB). I would like to take count for pagination but it takes more than 10 seconds every time. I had lots of investigation about query. I try to improve query. Try use IN, Sub Query, Join instead of where exist but I cannot improve anyway. Products table has ~241k and product_filter table has ~90m rows.

Here is Query:

select  count(*) as aggregate
    from  `products`
    where  `is_approved` = 1
      and  `published` = 1
      and  `is_b2b` = 0
      and  (Timestamp(`last_sale_date`) > '2020-09-25 08:16:26'
              or  `last_sale_date` is null
           )
      and  exists 
        ( SELECT  *
            from  `product_filter`
            where  `products`.`id` = `product_filter`.`product_id`
              and  `brand_id` = 35
              and  `brand_model_id` = 781
        )
      and  `product_type_id` = 1
      and  `products`.`deleted_at` is null;

Here are Schemes and Indexes of Tables

products table schema

CREATE TABLE `products` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `product_type_id` int unsigned NOT NULL DEFAULT '1',
  `product_display_type_id` int unsigned NOT NULL DEFAULT '1',
  `parent_product_id` int unsigned DEFAULT NULL,
  `is_b2b` tinyint(1) NOT NULL DEFAULT '0',
  `description` varchar(6000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `short_description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stock_quantity` int NOT NULL DEFAULT '0',
  `minimum_cart_quantity` int NOT NULL DEFAULT '1',
  `sku` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `merchant_id` int unsigned DEFAULT NULL,
  `price` double(8,2) DEFAULT NULL,
  `is_guaranteed` tinyint(1) NOT NULL DEFAULT '0',
  `guarantee_duration` int NOT NULL DEFAULT '0',
  `old_price` double(8,2) DEFAULT NULL,
  `currency_id` int unsigned NOT NULL DEFAULT '1',
  `currency_price` double(8,2) DEFAULT NULL,
  `currency_old_price` double(8,2) DEFAULT NULL,
  `estimated_shipping_date` int DEFAULT NULL,
  `free_shipping` tinyint(1) DEFAULT NULL,
  `published` tinyint(1) NOT NULL DEFAULT '0',
  `mark_as_new` tinyint(1) NOT NULL DEFAULT '0',
  `featured_product` tinyint(1) NOT NULL DEFAULT '0',
  `show_on_home_page` tinyint(1) NOT NULL DEFAULT '0',
  `manufacturer_id` int unsigned DEFAULT NULL,
  `name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `images` json DEFAULT NULL,
  `height` int DEFAULT NULL,
  `width` int DEFAULT NULL,
  `length` int DEFAULT NULL,
  `weight` int DEFAULT NULL,
  `authenticity` int NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `is_approved` tinyint(1) NOT NULL DEFAULT '1',
  `list_order` int NOT NULL DEFAULT '0',
  `crawled_at` timestamp NULL DEFAULT NULL,
  `merged_at` timestamp NULL DEFAULT NULL,
  `merge_end` tinyint(1) NOT NULL DEFAULT '0',
  `merge_start` tinyint(1) NOT NULL DEFAULT '0',
  `last_sale_date` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `product_condition` int NOT NULL DEFAULT '1',
  `slug` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `products_manufacturer_id_foreign` (`manufacturer_id`),
  KEY `products_product_display_type_id_foreign` (`product_display_type_id`),
  KEY `products_parent_product_id_foreign` (`parent_product_id`),
  KEY `products_merchant_id_foreign` (`merchant_id`),
  KEY `products_b2b_approved_published_ls_date_ptype_id_d_at_IDX` (`is_b2b`,`is_approved`,`published`,`last_sale_date`,`product_type_id`,`deleted_at`) USING BTREE,
  KEY `products_currency_id_foreign` (`currency_id`),
  KEY `products_search_1_IDX` (`is_approved`,`published`,`is_active`,`is_b2b`,`last_sale_date`,`product_type_id`,`deleted_at`) USING BTREE,
  KEY `products_product_type_id_is_b2b_merchant_id_deleted_at_IDX` (`product_type_id`,`is_b2b`,`merchant_id`,`deleted_at`) USING BTREE,
  KEY `products_p_t_id-is_b-is_apr-pbh-is_acv-lst_sl-mrnt_id-dltt_IDX` (`product_type_id`,`is_b2b`,`is_approved`,`published`,`is_active`,`last_sale_date`,`merchant_id`,`deleted_at`) USING BTREE,
  KEY `products_list_order_IDX` (`list_order`) USING BTREE,
  KEY `products_search_2_IDX` (`id`,`is_approved`,`published`,`is_active`,`is_b2b`,`last_sale_date`,`product_type_id`,`deleted_at`) USING BTREE,
  CONSTRAINT `products_currency_id_foreign` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `products_manufacturer_id_foreign` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`id`),
  CONSTRAINT `products_merchant_id_foreign` FOREIGN KEY (`merchant_id`) REFERENCES `users` (`id`),
  CONSTRAINT `products_parent_product_id_foreign` FOREIGN KEY (`parent_product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `products_product_display_type_id_foreign` FOREIGN KEY (`product_display_type_id`) REFERENCES `product_display_types` (`id`),
  CONSTRAINT `products_product_type_id_foreign` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=282754 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

product_filter table schema:

CREATE TABLE `product_filter` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `brand_id` int unsigned NOT NULL,
  `brand_model_id` int unsigned NOT NULL,
  `body_types_models_id` int unsigned NOT NULL,
  `body_types_models_years_id` int unsigned NOT NULL,
  `engine_years_id` int unsigned NOT NULL,
  `engine_years_horsepowers_id` int unsigned NOT NULL,
  `category_id` int unsigned DEFAULT NULL,
  `product_id` int unsigned NOT NULL,
  `product_vehicle_id` int unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_filter_brand_id_IDX` (`brand_id`) USING BTREE,
  KEY `product_filter_body_types_models_FK` (`body_types_models_id`),
  KEY `product_filter_body_types_models_years_FK` (`body_types_models_years_id`),
  KEY `product_filter_brand_models_FK` (`brand_model_id`),
  KEY `product_filter_engine_years_FK` (`engine_years_id`),
  KEY `product_filter_engine_years_horsepowers_FK` (`engine_years_horsepowers_id`),
  KEY `product_filter_categories_FK` (`category_id`),
  KEY `product_filter_product_vehicles_FK` (`product_vehicle_id`),
  KEY `product_filter_brand_id_model_id_IDX` (`brand_id`,`brand_model_id`) USING BTREE,
  KEY `product_filter_brand_id_product_id_IDX` (`brand_id`,`product_id`) USING BTREE,
  KEY `product_filter_product_id_brand_id_IDX` (`product_id`,`brand_id`) USING BTREE,
  KEY `product_filter_search_1_IDX` (`product_id`,`brand_id`,`brand_model_id`,`body_types_models_id`,`body_types_models_years_id`,`engine_years_id`,`engine_years_horsepowers_id`) USING BTREE,
  CONSTRAINT `product_filter_body_types_models_FK` FOREIGN KEY (`body_types_models_id`) REFERENCES `body_types_models` (`id`),
  CONSTRAINT `product_filter_body_types_models_years_FK` FOREIGN KEY (`body_types_models_years_id`) REFERENCES `body_types_models_years` (`id`),
  CONSTRAINT `product_filter_brand_models_FK` FOREIGN KEY (`brand_model_id`) REFERENCES `brand_models` (`id`),
  CONSTRAINT `product_filter_brands_FK` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`),
  CONSTRAINT `product_filter_categories_FK` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
  CONSTRAINT `product_filter_engine_years_FK` FOREIGN KEY (`engine_years_id`) REFERENCES `engine_years` (`id`),
  CONSTRAINT `product_filter_engine_years_horsepowers_FK` FOREIGN KEY (`engine_years_horsepowers_id`) REFERENCES `engine_years_horsepowers` (`id`),
  CONSTRAINT `product_filter_FK` FOREIGN KEY (`product_vehicle_id`) REFERENCES `product_vehicles` (`id`),
  CONSTRAINT `product_filter_products_FK` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101854289 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Query Explain Output:

id|select_type|table         |partitions|type|possible_keys                                                                                                                                                                                                                          |key                        |key_len|ref                             |rows  |filtered|Extra                            |
--|-----------|--------------|----------|----|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------------------|-------|--------------------------------|------|--------|---------------------------------|
 1|SIMPLE     |products      |          |ref |PRIMARY,products_b2b_approved_published_ls_date_ptype_id_d_at_IDX,products_search_1_IDX,products_product_type_id_is_b2b_merchant_id_deleted_at_IDX,products_p_t_id-is_b-is_apr-pbh-is_acv-lst_sl-mrnt_id-dltt_IDX,products_search_2_IDX|products_search_1_IDX      |2      |const,const                     |121055|       5|Using where; Using index         |
 1|SIMPLE     |product_filter|          |ref |product_filter_brand_id_IDX,product_filter_brand_models_FK,product_filter_brand_id_model_id_IDX,product_filter_brand_id_product_id_IDX,product_filter_product_id_brand_id_IDX,product_filter_search_1_IDX                              |product_filter_search_1_IDX|12     |otomodul.products.id,const,const|    58|     100|Using index; FirstMatch(products)|

Query Explain Analyze Output:

-> Aggregate: count(0)  (actual time=1568.310..1568.310 rows=1 loops=1)
    -> Nested loop semijoin  (cost=41715.82 rows=354199) (actual time=2.630..1566.071 rows=5575 loops=1)
        -> Filter: ((products.product_type_id = 1) and (products.is_b2b = 0) and ((cast(products.last_sale_date as datetime) > '2020-09-25 08:16:26') or (products.last_sale_date is null)) and (products.deleted_at is null))  (cost=745.59 rows=6053) (actual time=0.114..234.822 rows=190001 loops=1)
            -> Index lookup on products using products_search_1_IDX (is_approved=1, published=1)  (cost=745.59 rows=121055) (actual time=0.108..163.109 rows=190014 loops=1)
        -> Index lookup on product_filter using product_filter_search_1_IDX (product_id=products.id, brand_id=35, brand_model_id=781)  (cost=53.72 rows=59) (actual time=0.007..0.007 rows=0 loops=190001)
2
  • @Akina I have changed – Dreamcatcher Dec 7 '20 at 13:25
  • I have updated question with correct analyze outputs and query. Sorry. – Dreamcatcher Dec 7 '20 at 13:33
0

Problematic query text (formatted):

select count(*) as aggregate 
from `products` 
where `is_approved` = 1 
  and `published` = 1 
  and `is_b2b` = 0 
  and (Timestamp(`last_sale_date`) > '2020-09-25 08:16:26' or `last_sale_date` is null) 
  and exists (select * 
              from `product_filter` 
              where `products`.`id` = `product_filter`.`product_id` 
                and `brand_id` = 35 
                and `brand_model_id` = 781) 
  and `product_type_id` = 1 
  and `products`.`deleted_at` is null;

  1. last_sale_date is already TIMESTAMP. So replace
Timestamp(`last_sale_date`) > '2020-09-25 08:16:26'

with

`last_sale_date` > '2020-09-25 08:16:26'
  1. Create index
product_filter (brand_id, brand_model_id, product_id)

First 2 columns may be swapped.

  1. Create index
products (is_approved, published, is_b2b, product_type_id, deleted_at, last_sale_date, id)

First 5 columns may be reordered.

1
  • according to analyze result main bottleneck looks in products table (121055 row in rows column and (cost=41715.82 rows=354199) (actual time=2.630..1566.071 rows=5575 loops=1)). I have updated my question. As updated question main query and where exist query matched correct indexes but loop (where exists) take too long time. According your answer one problem is Timestamp method, I remove from query but it does not affect result time. – Dreamcatcher Dec 7 '20 at 14:42
0

There is no need for the Timestamp function in this:

      and  (Timestamp(`last_sale_date`) > '2020-09-25 08:16:26'

This might help:

  INDEX(is_approved, published, is_b2b, product_type_id, deleted_at)

DROP some of the unnecessary indexes. (There presence may actually hurt optimization):

INDEX(a,b)
INDEX(a)    -- DROP this; it contains exactly the first few column(s) of the other

Add this (for the inner query):

INDEX(brand_id, brand_model_id, product_id) -- (any order)

With that index (specifically with product_id last), I would try this formulation:

SELECT COUNT(*)
    FROM
    ( SELECT product_id
        from  `product_filter`
        where  `brand_id` = 35
          and  `brand_model_id` = 781
    ) AS f
    JOIN products AS p ON p.id = f.product_id
    WHERE  `is_approved` = 1
      and  `published` = 1
      and  `is_b2b` = 0
      and  `product_type_id` = 1
      and  `products`.`deleted_at` is null;
      and  `last_sale_date` > '2020-09-25 08:16:26'
              or  `last_sale_date` is null
           )
;

That might work better because the 'derived' table can very efficiently (given that index) find the list of likely product_ids. Then the rest is filtering for unlikely limitations.

But, if the table is full of "deleted/published/approved" items then I consider 'hard' deletes instead of 'soft' deletes. If necessary, move them to another table.

Use DECIMAL, not DOUBLE for monetary values. And double(8,2) is so lame that it has been removed from MySQL 8.0.xx.

Over-normalization? If engine_years is just a year, use YEAR not an id to another table. (Ditto for some other columns.)

The filters table might be 15GB (data + index). How big is innodb_buffer_pool_size?

INT takes 4 bytes; check to see if you could use a smaller datatype.

Do you really need all of create/update/delete timestamps?

-1

You might want to look into implementing Columnstore. This type of data structure is generally performant for analytical queries such as COUNT(*).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.