SELECT l.*, ld.layout_name,
                COUNT(DISTINCT s.seat_id) as total_seats,
                COUNT(DISTINCT sc.category_id) as total_categories
         FROM cscart_ec_seat_layouts l
         LEFT JOIN cscart_ec_seat_layout_descriptions ld ON l.layout_id = ld.layout_id AND ld.lang_code = 'en'
         LEFT JOIN cscart_ec_seats s ON l.layout_id = s.layout_id
         LEFT JOIN cscart_ec_seat_categories sc ON l.layout_id = sc.layout_id
         WHERE l.product_id = 12
         GROUP BY l.layout_id
         ORDER BY l.created_at DESC
         

Query is invalid

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "30.10"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "grouping_operation": {
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "l",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "idx_product_id"
              ],
              "key": "idx_product_id",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "4",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "index_condition": "(`pankajecarter_setup3`.`l`.`product_id` <=> 12)",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "0.20",
                "prefix_cost": "1.20",
                "data_read_per_join": "64"
              },
              "used_columns": [
                "layout_id",
                "product_id",
                "venue_data",
                "layout_config",
                "visual_elements",
                "version",
                "created_at",
                "updated_at",
                "status"
              ]
            }
          },
          {
            "table": {
              "table_name": "ld",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "layout_id",
                "lang_code"
              ],
              "key_length": "10",
              "ref": [
                "pankajecarter_setup3.l.layout_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.00",
                "eval_cost": "0.20",
                "prefix_cost": "2.40",
                "data_read_per_join": "784"
              },
              "used_columns": [
                "layout_id",
                "lang_code",
                "layout_name"
              ]
            }
          },
          {
            "table": {
              "table_name": "s",
              "access_type": "ref",
              "possible_keys": [
                "idx_layout_category"
              ],
              "key": "idx_layout_category",
              "used_key_parts": [
                "layout_id"
              ],
              "key_length": "4",
              "ref": [
                "pankajecarter_setup3.l.layout_id"
              ],
              "rows_examined_per_scan": 16,
              "rows_produced_per_join": 16,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "1.02",
                "eval_cost": "3.33",
                "prefix_cost": "6.76",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "seat_id",
                "layout_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "sc",
              "access_type": "ref",
              "possible_keys": [
                "idx_layout_id"
              ],
              "key": "idx_layout_id",
              "used_key_parts": [
                "layout_id"
              ],
              "key_length": "4",
              "ref": [
                "pankajecarter_setup3.l.layout_id"
              ],
              "rows_examined_per_scan": 2,
              "rows_produced_per_join": 33,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "16.68",
                "eval_cost": "6.67",
                "prefix_cost": "30.11",
                "data_read_per_join": "26K"
              },
              "used_columns": [
                "category_id",
                "layout_id"
              ]
            }
          }
        ]
      }
    }
  }
}