SELECT 
  COUNT(result.product_id) 
FROM 
  (
    SELECT 
      products.product_id 
    FROM 
      products as products 
      LEFT JOIN product_descriptions as descr1 ON descr1.product_id = products.product_id 
      AND descr1.lang_code = 'en' 
      LEFT JOIN product_prices as prices ON prices.product_id = products.product_id 
      AND prices.lower_limit = 1 
      LEFT JOIN companies AS companies ON companies.company_id = products.company_id 
      INNER JOIN products_categories as products_categories ON products_categories.product_id = products.product_id 
      INNER JOIN categories ON categories.category_id = products_categories.category_id 
      AND (
        categories.usergroup_ids = '' 
        OR FIND_IN_SET(0, categories.usergroup_ids) 
        OR FIND_IN_SET(1, categories.usergroup_ids)
      ) 
      AND categories.status IN ('A', 'H') 
      AND categories.storefront_id IN (0, 1) 
      LEFT JOIN product_options_inventory as inventory ON inventory.product_id = products.product_id 
      LEFT JOIN product_popularity as popularity ON popularity.product_id = products.product_id 
    WHERE 
      1 
      AND categories.category_id IN (9500) 
      AND companies.status IN ('A') 
      AND products.company_id = 242 
      AND (
        products.amount > 0 
        OR products.tracking = 'D'
      ) 
      AND (
        products.usergroup_ids = '' 
        OR FIND_IN_SET(0, products.usergroup_ids) 
        OR FIND_IN_SET(1, products.usergroup_ids)
      ) 
      AND products.status IN ('A') 
      AND prices.usergroup_id IN (0, 0, 1) 
      AND products.company_id = 242 
      AND products.company_id = 242 
      AND products.company_id = 242 
    GROUP BY 
      products.product_id
  ) as result

Query time 0.00127

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.72"
    },
    "table": {
      "table_name": "result",
      "access_type": "ALL",
      "rows_examined_per_scan": 2,
      "rows_produced_per_join": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "2.52",
        "eval_cost": "0.20",
        "prefix_cost": "2.73",
        "data_read_per_join": "32"
      },
      "used_columns": [
        "product_id"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "2.76"
          },
          "grouping_operation": {
            "using_temporary_table": true,
            "using_filesort": false,
            "nested_loop": [
              {
                "table": {
                  "table_name": "companies",
                  "access_type": "const",
                  "possible_keys": [
                    "PRIMARY",
                    "idx_status"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "company_id"
                  ],
                  "key_length": "4",
                  "ref": [
                    "const"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 1,
                  "filtered": "100.00",
                  "cost_info": {
                    "read_cost": "0.00",
                    "eval_cost": "0.10",
                    "prefix_cost": "0.00",
                    "data_read_per_join": "14K"
                  },
                  "used_columns": [
                    "company_id",
                    "status"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "categories",
                  "access_type": "const",
                  "possible_keys": [
                    "PRIMARY",
                    "c_status",
                    "p_category_id"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "category_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "const"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 1,
                  "filtered": "100.00",
                  "cost_info": {
                    "read_cost": "0.00",
                    "eval_cost": "0.10",
                    "prefix_cost": "0.00",
                    "data_read_per_join": "2K"
                  },
                  "used_columns": [
                    "category_id",
                    "usergroup_ids",
                    "status",
                    "storefront_id"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "products_categories",
                  "access_type": "ref",
                  "possible_keys": [
                    "PRIMARY",
                    "pt"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "category_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "const"
                  ],
                  "rows_examined_per_scan": 5,
                  "rows_produced_per_join": 5,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "0.27",
                    "eval_cost": "0.50",
                    "prefix_cost": "0.77",
                    "data_read_per_join": "80"
                  },
                  "used_columns": [
                    "product_id",
                    "category_id"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "products",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY",
                    "age_verification",
                    "status",
                    "idx_company_id",
                    "idx_usergroup_ids",
                    "idx_amount",
                    "index_products_on_deleted_at"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "product_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "goimagine.products_categories.product_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 0,
                  "filtered": "5.00",
                  "cost_info": {
                    "read_cost": "1.26",
                    "eval_cost": "0.03",
                    "prefix_cost": "2.53",
                    "data_read_per_join": "1K"
                  },
                  "used_columns": [
                    "product_id",
                    "status",
                    "company_id",
                    "amount",
                    "usergroup_ids",
                    "tracking"
                  ],
                  "attached_condition": "((`goimagine`.`products`.`company_id` = 242) and ((`goimagine`.`products`.`amount` > 0) or (`goimagine`.`products`.`tracking` = 'D')) and ((`goimagine`.`products`.`usergroup_ids` = '') or (0 <> find_in_set(0,`goimagine`.`products`.`usergroup_ids`)) or (0 <> find_in_set(1,`goimagine`.`products`.`usergroup_ids`))) and (`goimagine`.`products`.`status` = 'A'))"
                }
              },
              {
                "table": {
                  "table_name": "prices",
                  "access_type": "ref",
                  "possible_keys": [
                    "usergroup",
                    "product_id",
                    "lower_limit",
                    "usergroup_id"
                  ],
                  "key": "product_id",
                  "used_key_parts": [
                    "product_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "goimagine.products_categories.product_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 0,
                  "filtered": "25.00",
                  "cost_info": {
                    "read_cost": "0.08",
                    "eval_cost": "0.01",
                    "prefix_cost": "2.64",
                    "data_read_per_join": "1"
                  },
                  "used_columns": [
                    "id",
                    "product_id",
                    "lower_limit",
                    "usergroup_id"
                  ],
                  "attached_condition": "((`goimagine`.`prices`.`lower_limit` = 1) and (`goimagine`.`prices`.`usergroup_id` in (0,0,1)))"
                }
              },
              {
                "table": {
                  "table_name": "descr1",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY",
                    "product_id"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "product_id",
                    "lang_code"
                  ],
                  "key_length": "11",
                  "ref": [
                    "goimagine.products_categories.product_id",
                    "const"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 0,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "0.04",
                    "eval_cost": "0.01",
                    "prefix_cost": "2.68",
                    "data_read_per_join": "337"
                  },
                  "used_columns": [
                    "product_id",
                    "lang_code"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "inventory",
                  "access_type": "ref",
                  "possible_keys": [
                    "pc"
                  ],
                  "key": "pc",
                  "used_key_parts": [
                    "product_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "goimagine.products_categories.product_id"
                  ],
                  "rows_examined_per_scan": 3,
                  "rows_produced_per_join": 0,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "0.02",
                    "eval_cost": "0.02",
                    "prefix_cost": "2.73",
                    "data_read_per_join": "214"
                  },
                  "used_columns": [
                    "product_id",
                    "combination_hash"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "popularity",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY",
                    "total"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "product_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "goimagine.products_categories.product_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 0,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "0.02",
                    "eval_cost": "0.02",
                    "prefix_cost": "2.77",
                    "data_read_per_join": "7"
                  },
                  "used_columns": [
                    "product_id"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
}