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_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 1.23566

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1215710.60"
    },
    "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": 160564,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "118.54",
                "eval_cost": "32112.80",
                "prefix_cost": "32231.34",
                "data_read_per_join": "1G"
              },
              "used_columns": [
                "product_id",
                "status"
              ]
            }
          },
          {
            "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": 266447,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "160719.03",
                "eval_cost": "53289.41",
                "prefix_cost": "246239.78",
                "data_read_per_join": "4M"
              },
              "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": 266447,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "266447.06",
                "eval_cost": "53289.41",
                "prefix_cost": "565976.25",
                "data_read_per_join": "1G"
              },
              "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": 292100,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "266447.06",
                "eval_cost": "58420.12",
                "prefix_cost": "890843.42",
                "data_read_per_join": "4M"
              },
              "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`,`webmarco`.`cscart_products`.`product_id` in ( <materialize> (/* select#2 */ select `webmarco`.`cscart_product_variation_group_products`.`product_id` from `webmarco`.`cscart_product_variation_group_products` where 1 ), <primary_index_lookup>(`webmarco`.`cscart_products`.`product_id` in <temporary table> on <auto_key> where ((`webmarco`.`cscart_products`.`product_id` = `materialized-subquery`.`product_id`)))))))), true)",
              "attached_subqueries": [
                {
                  "table": {
                    "table_name": "<materialized_subquery>",
                    "access_type": "eq_ref",
                    "key": "<auto_key>",
                    "key_length": "3",
                    "rows_examined_per_scan": 1,
                    "materialized_from_subquery": {
                      "using_temporary_table": true,
                      "dependent": true,
                      "cacheable": false,
                      "query_block": {
                        "select_id": 2,
                        "cost_info": {
                          "query_cost": "18099.80"
                        },
                        "table": {
                          "table_name": "cscart_product_variation_group_products",
                          "access_type": "index",
                          "possible_keys": [
                            "PRIMARY"
                          ],
                          "key": "idx_group_id",
                          "used_key_parts": [
                            "group_id"
                          ],
                          "key_length": "3",
                          "rows_examined_per_scan": 88734,
                          "rows_produced_per_join": 88734,
                          "filtered": "100.00",
                          "using_index": true,
                          "cost_info": {
                            "read_cost": "353.00",
                            "eval_cost": "17746.80",
                            "prefix_cost": "18099.80",
                            "data_read_per_join": "1M"
                          },
                          "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": 292100,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "266447.06",
                "eval_cost": "58420.12",
                "prefix_cost": "1215710.60",
                "data_read_per_join": "1G"
              },
              "used_columns": [
                "category_id",
                "lang_code",
                "category"
              ]
            }
          }
        ]
      }
    }
  }
}