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.08257

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1215716.80"
    },
    "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": 160561,
              "rows_produced_per_join": 160561,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "118.54",
                "eval_cost": "32112.20",
                "prefix_cost": "32230.74",
                "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": 266439,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "160716.02",
                "eval_cost": "53287.86",
                "prefix_cost": "246234.62",
                "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": 266439,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "266439.32",
                "eval_cost": "53287.86",
                "prefix_cost": "565961.81",
                "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": 292190,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "266439.32",
                "eval_cost": "58438.17",
                "prefix_cost": "890839.30",
                "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": "18105.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": 88764,
                          "rows_produced_per_join": 88764,
                          "filtered": "100.00",
                          "using_index": true,
                          "cost_info": {
                            "read_cost": "353.00",
                            "eval_cost": "17752.80",
                            "prefix_cost": "18105.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": 292190,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "266439.32",
                "eval_cost": "58438.17",
                "prefix_cost": "1215716.80",
                "data_read_per_join": "1G"
              },
              "used_columns": [
                "category_id",
                "lang_code",
                "category"
              ]
            }
          }
        ]
      }
    }
  }
}