Improve Slow Count Query

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)