SELECT 
  COUNT(*) as total 
FROM 
  (
    SELECT 
      `C`.`services` 
    FROM 
      `cscart_products` AS `P` 
      LEFT JOIN `cscart_guest__wishlist` AS `GW` ON `P`.`product_id` = `GW`.`product_id` 
      AND `GW`.`guest_id` = 0 
      LEFT JOIN `cscart_guest__compare` AS `GC` ON `P`.`product_id` = `GC`.`product_id` 
      AND `GC`.`guest_id` = 0 
      LEFT JOIN `cscart_product_stocks` AS `PST` ON `P`.`product_id` = `PST`.`product_id` 
      LEFT JOIN `cscart_company_stocks` AS `CST` ON `PST`.`stock_id` = `CST`.`stock_id` 
      LEFT JOIN `cscart_custom_city` AS `CITY` ON `CITY`.`fias_id` = `CST`.`fias_id` 
      LEFT JOIN `cscart_product_prices_history` AS `HP` ON `HP`.`product_id` = `P`.`product_id` 
      LEFT JOIN `cscart_products__images` AS `IMG` ON `P`.`product_id` = `IMG`.`product_id` 
      LEFT JOIN `files` AS `FILE` ON `FILE`.`id` = `IMG`.`watermark_small_file_id` 
      LEFT JOIN `cscart_companies` AS `COM` ON `COM`.`company_id` = `P`.`company_id` 
      INNER JOIN `cscart_products_categories` AS `PC` ON `PC`.`product_id` = `P`.`product_id` 
      AND `PC`.`link_type` = 'M' 
      INNER JOIN `cscart_categories` AS `C` ON `C`.`category_id` = `PC`.`category_id` 
      INNER JOIN `cscart_category_descriptions` AS `CD` ON `C`.`category_id` = `CD`.`category_id` 
      LEFT JOIN `cscart_product_prices` AS `PP` ON `PP`.`product_id` = `P`.`product_id` 
      LEFT JOIN `cscart_product_descriptions` AS `PD` ON `PD`.`product_id` = `P`.`product_id` 
      AND `PD`.`lang_code` = 'ru' 
      LEFT JOIN `cscart_currencies` AS `CUR` ON `PP`.`currency` = `CUR`.`currency_code` 
      LEFT JOIN `cscart_brands` AS `B` ON `P`.`brand_id` = `B`.`brand_id` 
    WHERE 
      1 
      AND `P`.`status` = 'A' 
      AND `P`.`company_id` IN (102) 
      AND `COM`.`status` = 'A' 
    GROUP BY 
      `P`.`product_id`
  ) AS counted;

Query time 0.02014

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5272228.49"
    },
    "table": {
      "table_name": "counted",
      "access_type": "ALL",
      "rows_examined_per_scan": 46864231,
      "rows_produced_per_join": 46864231,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "585805.39",
        "eval_cost": "4686423.10",
        "prefix_cost": "5272228.49",
        "data_read_per_join": "715M"
      },
      "used_columns": [
        "services"
      ],
      "materialized_from_subquery": {
        "using_temporary_table": true,
        "dependent": false,
        "cacheable": true,
        "query_block": {
          "select_id": 2,
          "cost_info": {
            "query_cost": "32136837.23"
          },
          "grouping_operation": {
            "using_temporary_table": true,
            "using_filesort": false,
            "nested_loop": [
              {
                "table": {
                  "table_name": "COM",
                  "access_type": "const",
                  "possible_keys": [
                    "PRIMARY"
                  ],
                  "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": "7K"
                  },
                  "used_columns": [
                    "company_id",
                    "status"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "P",
                  "access_type": "ref",
                  "possible_keys": [
                    "PRIMARY",
                    "age_verification",
                    "status",
                    "idx_parent_product_id",
                    "cscart_products_brand_id_index",
                    "cscart_products_conditions_index",
                    "cscart_products_real_company_id_index",
                    "cscart_products_product_id_index",
                    "idx_status"
                  ],
                  "key": "status",
                  "used_key_parts": [
                    "status"
                  ],
                  "key_length": "3",
                  "ref": [
                    "const"
                  ],
                  "rows_examined_per_scan": 12561,
                  "rows_produced_per_join": 1256,
                  "filtered": "10.00",
                  "cost_info": {
                    "read_cost": "312.75",
                    "eval_cost": "125.61",
                    "prefix_cost": "1568.85",
                    "data_read_per_join": "9M"
                  },
                  "used_columns": [
                    "product_id",
                    "status",
                    "company_id",
                    "brand_id"
                  ],
                  "attached_condition": "(`dev2`.`P`.`company_id` = 102)"
                }
              },
              {
                "table": {
                  "table_name": "PP",
                  "access_type": "ref",
                  "possible_keys": [
                    "product_id"
                  ],
                  "key": "product_id",
                  "used_key_parts": [
                    "product_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "dev2.P.product_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 1256,
                  "filtered": "100.00",
                  "cost_info": {
                    "read_cost": "314.03",
                    "eval_cost": "125.61",
                    "prefix_cost": "2008.49",
                    "data_read_per_join": "166K"
                  },
                  "used_columns": [
                    "product_id",
                    "currency"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "PD",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY",
                    "product_id"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "product_id",
                    "lang_code"
                  ],
                  "key_length": "11",
                  "ref": [
                    "dev2.P.product_id",
                    "const"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 1256,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "1060.91",
                    "eval_cost": "125.61",
                    "prefix_cost": "3195.01",
                    "data_read_per_join": "7M"
                  },
                  "used_columns": [
                    "product_id",
                    "lang_code"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "CUR",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "currency_code"
                  ],
                  "key": "currency_code",
                  "used_key_parts": [
                    "currency_code"
                  ],
                  "key_length": "12",
                  "ref": [
                    "dev2.PP.currency"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 1256,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "314.03",
                    "eval_cost": "125.61",
                    "prefix_cost": "3634.64",
                    "data_read_per_join": "117K"
                  },
                  "used_columns": [
                    "currency_id",
                    "currency_code"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "B",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "brand_id"
                  ],
                  "key_length": "4",
                  "ref": [
                    "dev2.P.brand_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 1256,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "314.03",
                    "eval_cost": "125.61",
                    "prefix_cost": "4074.28",
                    "data_read_per_join": "4M"
                  },
                  "used_columns": [
                    "brand_id"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "PC",
                  "access_type": "ref",
                  "possible_keys": [
                    "PRIMARY",
                    "link_type",
                    "pt",
                    "idx_pc_product_id_type"
                  ],
                  "key": "pt",
                  "used_key_parts": [
                    "product_id",
                    "link_type"
                  ],
                  "key_length": "6",
                  "ref": [
                    "dev2.P.product_id",
                    "const"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 1268,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "852.37",
                    "eval_cost": "126.86",
                    "prefix_cost": "5053.51",
                    "data_read_per_join": "19K"
                  },
                  "used_columns": [
                    "product_id",
                    "category_id",
                    "link_type"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "C",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY",
                    "p_category_id"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "category_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "dev2.PC.category_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 1268,
                  "filtered": "100.00",
                  "cost_info": {
                    "read_cost": "800.70",
                    "eval_cost": "126.86",
                    "prefix_cost": "5981.08",
                    "data_read_per_join": "13M"
                  },
                  "used_columns": [
                    "category_id",
                    "services"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "CD",
                  "access_type": "ref",
                  "possible_keys": [
                    "PRIMARY"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "category_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "dev2.PC.category_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 1268,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "317.16",
                    "eval_cost": "126.86",
                    "prefix_cost": "6425.10",
                    "data_read_per_join": "5M"
                  },
                  "used_columns": [
                    "category_id",
                    "lang_code"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "GW",
                  "access_type": "ALL",
                  "rows_examined_per_scan": 48,
                  "rows_produced_per_join": 60895,
                  "filtered": "100.00",
                  "using_join_buffer": "hash join",
                  "cost_info": {
                    "read_cost": "0.35",
                    "eval_cost": "6089.51",
                    "prefix_cost": "12514.96",
                    "data_read_per_join": "951K"
                  },
                  "used_columns": [
                    "wishlist_id",
                    "product_id",
                    "guest_id"
                  ],
                  "attached_condition": "<if>(is_not_null_compl(GW), ((`dev2`.`GW`.`guest_id` = 0) and (`dev2`.`P`.`product_id` = `dev2`.`GW`.`product_id`)), true)"
                }
              },
              {
                "table": {
                  "table_name": "GC",
                  "access_type": "ALL",
                  "rows_examined_per_scan": 88,
                  "rows_produced_per_join": 5358769,
                  "filtered": "100.00",
                  "using_join_buffer": "hash join",
                  "cost_info": {
                    "read_cost": "5.36",
                    "eval_cost": "535876.91",
                    "prefix_cost": "548397.23",
                    "data_read_per_join": "81M"
                  },
                  "used_columns": [
                    "compare_id",
                    "guest_id",
                    "product_id"
                  ],
                  "attached_condition": "<if>(is_not_null_compl(GC), ((`dev2`.`GC`.`guest_id` = 0) and (`dev2`.`P`.`product_id` = `dev2`.`GC`.`product_id`)), true)"
                }
              },
              {
                "table": {
                  "table_name": "PST",
                  "access_type": "ref",
                  "possible_keys": [
                    "cscart_product_stocks_pk"
                  ],
                  "key": "cscart_product_stocks_pk",
                  "used_key_parts": [
                    "product_id"
                  ],
                  "key_length": "4",
                  "ref": [
                    "dev2.P.product_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 5512191,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "3265682.14",
                    "eval_cost": "551219.14",
                    "prefix_cost": "4365298.51",
                    "data_read_per_join": "126M"
                  },
                  "used_columns": [
                    "product_id",
                    "stock_id"
                  ],
                  "attached_condition": "<if>(is_not_null_compl(PST), (`dev2`.`P`.`product_id` = `dev2`.`PST`.`product_id`), true)"
                }
              },
              {
                "table": {
                  "table_name": "CST",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "stock_id"
                  ],
                  "key_length": "4",
                  "ref": [
                    "dev2.PST.stock_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 5512191,
                  "filtered": "100.00",
                  "cost_info": {
                    "read_cost": "323.02",
                    "eval_cost": "551219.14",
                    "prefix_cost": "4916840.66",
                    "data_read_per_join": "2G"
                  },
                  "used_columns": [
                    "stock_id",
                    "fias_id"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "CITY",
                  "access_type": "ref",
                  "possible_keys": [
                    "idx_custom_city_fias_id"
                  ],
                  "key": "idx_custom_city_fias_id",
                  "used_key_parts": [
                    "fias_id"
                  ],
                  "key_length": "386",
                  "ref": [
                    "dev2.CST.fias_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 5512191,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "1378047.84",
                    "eval_cost": "551219.14",
                    "prefix_cost": "6846107.64",
                    "data_read_per_join": "27G"
                  },
                  "used_columns": [
                    "city_id",
                    "fias_id"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "HP",
                  "access_type": "ref",
                  "possible_keys": [
                    "idx_hp_product_id"
                  ],
                  "key": "idx_hp_product_id",
                  "used_key_parts": [
                    "product_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "dev2.P.product_id"
                  ],
                  "rows_examined_per_scan": 2,
                  "rows_produced_per_join": 14100794,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "2789122.07",
                    "eval_cost": "1410079.45",
                    "prefix_cost": "11045309.15",
                    "data_read_per_join": "3G"
                  },
                  "used_columns": [
                    "id",
                    "product_id"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "IMG",
                  "access_type": "ref",
                  "possible_keys": [
                    "product_id"
                  ],
                  "key": "product_id",
                  "used_key_parts": [
                    "product_id"
                  ],
                  "key_length": "3",
                  "ref": [
                    "dev2.P.product_id"
                  ],
                  "rows_examined_per_scan": 3,
                  "rows_produced_per_join": 46864231,
                  "filtered": "100.00",
                  "cost_info": {
                    "read_cost": "11716057.82",
                    "eval_cost": "4686423.13",
                    "prefix_cost": "27447790.11",
                    "data_read_per_join": "5G"
                  },
                  "used_columns": [
                    "product_id",
                    "watermark_small_file_id"
                  ]
                }
              },
              {
                "table": {
                  "table_name": "FILE",
                  "access_type": "eq_ref",
                  "possible_keys": [
                    "PRIMARY"
                  ],
                  "key": "PRIMARY",
                  "used_key_parts": [
                    "id"
                  ],
                  "key_length": "4",
                  "ref": [
                    "dev2.IMG.watermark_small_file_id"
                  ],
                  "rows_examined_per_scan": 1,
                  "rows_produced_per_join": 46864231,
                  "filtered": "100.00",
                  "using_index": true,
                  "cost_info": {
                    "read_cost": "2623.99",
                    "eval_cost": "4686423.13",
                    "prefix_cost": "32136837.23",
                    "data_read_per_join": "166G"
                  },
                  "used_columns": [
                    "id"
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
}