SELECT 
  COUNT(*) as count, 
  cscart_category_descriptions.category, 
  cscart_category_descriptions.category_id 
FROM 
  cscart_products_categories 
  JOIN cscart_products ON cscart_products_categories.product_id = cscart_products.product_id 
  JOIN cscart_categories ON cscart_products_categories.category_id = cscart_categories.category_id 
  JOIN cscart_category_descriptions ON cscart_products_categories.category_id = cscart_category_descriptions.category_id 
  LEFT JOIN cscart_product_variation_group_products ON cscart_products_categories.product_id = cscart_product_variation_group_products.product_id 
WHERE 
  1 
  AND cscart_products.company_id = 298 
  AND cscart_category_descriptions.lang_code = 'en' 
  AND cscart_products.status = 'A' 
  AND (
    cscart_product_variation_group_products.parent_product_id = 0 
    OR cscart_products_categories.product_id not in (
      SELECT 
        cscart_product_variation_group_products.product_id 
      FROM 
        cscart_product_variation_group_products 
      WHERE 
        1
    )
  ) 
GROUP BY 
  cscart_products_categories.category_id 
ORDER BY 
  cscart_categories.position asc

Query time 0.38223

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "168435.90"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "cscart_products",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "status"
              ],
              "key": "status",
              "used_key_parts": [
                "status"
              ],
              "key_length": "3",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 160564,
              "rows_produced_per_join": 16056,
              "filtered": "10.00",
              "cost_info": {
                "read_cost": "17970.00",
                "eval_cost": "3211.28",
                "prefix_cost": "50082.80",
                "data_read_per_join": "122M"
              },
              "used_columns": [
                "product_id",
                "status",
                "company_id"
              ],
              "attached_condition": "(`webmarco`.`cscart_products`.`company_id` = 298)"
            }
          },
          {
            "table": {
              "table_name": "cscart_products_categories",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "link_type",
                "pt"
              ],
              "key": "pt",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "webmarco.cscart_products.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 26645,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "16071.90",
                "eval_cost": "5329.01",
                "prefix_cost": "71483.71",
                "data_read_per_join": "416K"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_categories",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "webmarco.cscart_products_categories.category_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 26645,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "26645.03",
                "eval_cost": "5329.01",
                "prefix_cost": "103457.74",
                "data_read_per_join": "154M"
              },
              "used_columns": [
                "category_id",
                "position"
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_product_variation_group_products",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "webmarco.cscart_products.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 29220,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "26645.03",
                "eval_cost": "5844.06",
                "prefix_cost": "135946.82",
                "data_read_per_join": "456K"
              },
              "used_columns": [
                "product_id",
                "parent_product_id",
                "group_id"
              ],
              "attached_condition": "<if>(found_match(cscart_product_variation_group_products), ((`webmarco`.`cscart_product_variation_group_products`.`parent_product_id` = 0) or (not(<in_optimizer>(`webmarco`.`cscart_products`.`product_id`,<exists>(<index_lookup>(<cache>(`webmarco`.`cscart_products_categories`.`product_id`) in cscart_product_variation_group_products on PRIMARY)))))), true)",
              "attached_subqueries": [
                {
                  "dependent": true,
                  "cacheable": false,
                  "query_block": {
                    "select_id": 2,
                    "cost_info": {
                      "query_cost": "1.22"
                    },
                    "table": {
                      "table_name": "cscart_product_variation_group_products",
                      "access_type": "index_subquery",
                      "possible_keys": [
                        "PRIMARY"
                      ],
                      "key": "PRIMARY",
                      "used_key_parts": [
                        "product_id"
                      ],
                      "key_length": "3",
                      "ref": [
                        "func"
                      ],
                      "rows_examined_per_scan": 1,
                      "rows_produced_per_join": 1,
                      "filtered": "100.00",
                      "using_index": true,
                      "cost_info": {
                        "read_cost": "1.00",
                        "eval_cost": "0.22",
                        "prefix_cost": "1.22",
                        "data_read_per_join": "17"
                      },
                      "used_columns": [
                        "product_id"
                      ]
                    }
                  }
                }
              ]
            }
          },
          {
            "table": {
              "table_name": "cscart_category_descriptions",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id",
                "lang_code"
              ],
              "key_length": "9",
              "ref": [
                "webmarco.cscart_products_categories.category_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 29220,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "26645.03",
                "eval_cost": "5844.06",
                "prefix_cost": "168435.90",
                "data_read_per_join": "116M"
              },
              "used_columns": [
                "category_id",
                "lang_code",
                "category"
              ]
            }
          }
        ]
      }
    }
  }
}