SELECT SQL_CALC_FOUND_ROWS DISTINCT `C`.`services`,`C`.`category_id`,`CD`.`category`,`COM`.`company`,`CUR`.`price` AS `currency_rate`,IF(`HP`.`created_at`, MAX(`HP`.`created_at`), `P`.`timestamp`) AS `price_update_time`,`P`.`amount`,`P`.`city_id`,`P`.`brand_id`,`P`.`company_id`,`P`.`city_place`,`P`.`conditions`,`P`.`guarantee_id`,`P`.`type_of_sale`,`P`.`product_code`,`P`.`product_id`,`P`.`status`,`P`.`year`,`P`.`uniqueSale`,`P`.`is_confiscated`,`P`.`updated_timestamp`,`PC`.`category_id` AS `main_category`,`PD`.`full_description`,`PD`.`short_description`,`PD`.`leasingCount`,`PD`.`leasingPrice`,`PD`.`product`,`PP`.`currency`,`PP`.`discount_percent`,`PP`.`price`,`PP`.`recycling_price_rub`,((PP.price * COALESCE(CUR.price, 1)) * (1 - PP.discount_percent / 100) + PP.recycling_price_rub) AS total_price,CASE WHEN `GC`.`compare_id` IS NOT NULL THEN "Y" ELSE "N" END AS `compare`,CASE WHEN `GW`.`wishlist_id` IS NOT NULL THEN "Y" ELSE "N" END AS `wish`,IF(`CITY`.`city_id` IS NULL, 0, 1) as city_priority,CITY.address,(select count(*) from `cscart_product_stocks` where product_id = P.product_id) as stockCount,`B`.`brand`,`B`.`has_subsidy`,COALESCE(FILE.path, "") AS image,COALESCE(IMG.alt, "") AS image_alt 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 `C`.`category_id` IN (559) AND `P`.`status` = 'A' AND `COM`.`status` = 'A' GROUP BY `P`.`product_id` ORDER BY city_priority DESC,sort_index DESC LIMIT 0, 11

Query is invalid

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "138442.33"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "C",
              "access_type": "const",
              "possible_keys": [
                "PRIMARY",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "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": "10K"
              },
              "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": [
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "0.25",
                "eval_cost": "0.10",
                "prefix_cost": "0.35",
                "data_read_per_join": "4K"
              },
              "used_columns": [
                "category_id",
                "lang_code",
                "category"
              ]
            }
          },
          {
            "table": {
              "table_name": "PC",
              "access_type": "ref",
              "possible_keys": [
                "PRIMARY",
                "link_type",
                "pt",
                "idx_pc_product_id_type"
              ],
              "key": "link_type",
              "used_key_parts": [
                "link_type",
                "category_id"
              ],
              "key_length": "6",
              "ref": [
                "const",
                "const"
              ],
              "rows_examined_per_scan": 64,
              "rows_produced_per_join": 64,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "0.27",
                "eval_cost": "6.40",
                "prefix_cost": "7.02",
                "data_read_per_join": "1024"
              },
              "used_columns": [
                "product_id",
                "category_id",
                "link_type"
              ]
            }
          },
          {
            "table": {
              "table_name": "P",
              "access_type": "eq_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": "PRIMARY",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 46,
              "filtered": "72.23",
              "cost_info": {
                "read_cost": "16.00",
                "eval_cost": "4.62",
                "prefix_cost": "29.42",
                "data_read_per_join": "348K"
              },
              "used_columns": [
                "product_id",
                "product_code",
                "status",
                "company_id",
                "amount",
                "timestamp",
                "updated_timestamp",
                "sort_index",
                "guarantee_id",
                "conditions",
                "year",
                "city_place",
                "city_id",
                "brand_id",
                "uniqueSale",
                "is_confiscated",
                "type_of_sale"
              ],
              "attached_condition": "(`dev2`.`P`.`status` = 'A')"
            }
          },
          {
            "table": {
              "table_name": "COM",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "company_id"
              ],
              "key_length": "4",
              "ref": [
                "dev2.P.company_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 4,
              "filtered": "10.00",
              "cost_info": {
                "read_cost": "11.56",
                "eval_cost": "0.46",
                "prefix_cost": "45.60",
                "data_read_per_join": "34K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ],
              "attached_condition": "(`dev2`.`COM`.`status` = 'A')"
            }
          },
          {
            "table": {
              "table_name": "PP",
              "access_type": "ref",
              "possible_keys": [
                "usergroup",
                "product_id"
              ],
              "key": "usergroup",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 4,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.30",
                "eval_cost": "0.46",
                "prefix_cost": "47.36",
                "data_read_per_join": "1K"
              },
              "used_columns": [
                "product_id",
                "price",
                "currency",
                "recycling_price_rub",
                "discount_percent",
                "lower_limit",
                "usergroup_id"
              ]
            }
          },
          {
            "table": {
              "table_name": "PD",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "product_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "product_id",
                "lang_code"
              ],
              "key_length": "9",
              "ref": [
                "dev2.PC.product_id",
                "const"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 4,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "3.85",
                "eval_cost": "0.46",
                "prefix_cost": "51.67",
                "data_read_per_join": "21K"
              },
              "used_columns": [
                "product_id",
                "lang_code",
                "product",
                "short_description",
                "full_description",
                "leasingCount",
                "leasingPrice"
              ]
            }
          },
          {
            "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": 4,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.16",
                "eval_cost": "0.46",
                "prefix_cost": "53.29",
                "data_read_per_join": "443"
              },
              "used_columns": [
                "currency_id",
                "currency_code",
                "price"
              ]
            }
          },
          {
            "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": 4,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.16",
                "eval_cost": "0.46",
                "prefix_cost": "54.90",
                "data_read_per_join": "15K"
              },
              "used_columns": [
                "brand_id",
                "brand",
                "has_subsidy"
              ]
            }
          },
          {
            "table": {
              "table_name": "GW",
              "access_type": "ALL",
              "rows_examined_per_scan": 48,
              "rows_produced_per_join": 221,
              "filtered": "100.00",
              "using_join_buffer": "hash join",
              "cost_info": {
                "read_cost": "0.27",
                "eval_cost": "22.19",
                "prefix_cost": "77.36",
                "data_read_per_join": "3K"
              },
              "used_columns": [
                "wishlist_id",
                "product_id",
                "guest_id"
              ],
              "attached_condition": "<if>(is_not_null_compl(GW), ((`dev2`.`GW`.`guest_id` = 0) and (`dev2`.`PC`.`product_id` = `dev2`.`GW`.`product_id`)), true)"
            }
          },
          {
            "table": {
              "table_name": "GC",
              "access_type": "ALL",
              "rows_examined_per_scan": 88,
              "rows_produced_per_join": 19525,
              "filtered": "100.00",
              "using_join_buffer": "hash join",
              "cost_info": {
                "read_cost": "0.98",
                "eval_cost": "1952.53",
                "prefix_cost": "2030.86",
                "data_read_per_join": "305K"
              },
              "used_columns": [
                "compare_id",
                "guest_id",
                "product_id"
              ],
              "attached_condition": "<if>(is_not_null_compl(GC), ((`dev2`.`GC`.`guest_id` = 0) and (`dev2`.`PC`.`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.PC.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 20086,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "8542.80",
                "eval_cost": "2008.63",
                "prefix_cost": "12582.29",
                "data_read_per_join": "470K"
              },
              "used_columns": [
                "product_id",
                "stock_id"
              ],
              "attached_condition": "<if>(is_not_null_compl(PST), (`dev2`.`PC`.`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": 20086,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1.19",
                "eval_cost": "2008.63",
                "prefix_cost": "14592.11",
                "data_read_per_join": "7M"
              },
              "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": 20086,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "5021.57",
                "eval_cost": "2008.63",
                "prefix_cost": "21622.30",
                "data_read_per_join": "104M"
              },
              "used_columns": [
                "city_id",
                "address",
                "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.PC.product_id"
              ],
              "rows_examined_per_scan": 2,
              "rows_produced_per_join": 49736,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "23873.55",
                "eval_cost": "4973.66",
                "prefix_cost": "50469.50",
                "data_read_per_join": "11M"
              },
              "used_columns": [
                "id",
                "product_id",
                "created_at"
              ]
            }
          },
          {
            "table": {
              "table_name": "IMG",
              "access_type": "ref",
              "possible_keys": [
                "product_id"
              ],
              "key": "product_id",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.product_id"
              ],
              "rows_examined_per_scan": 3,
              "rows_produced_per_join": 195469,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "48867.50",
                "eval_cost": "19547.00",
                "prefix_cost": "118884.00",
                "data_read_per_join": "5M"
              },
              "used_columns": [
                "product_id",
                "watermark_small_file_id",
                "alt"
              ]
            }
          },
          {
            "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": 195469,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "11.33",
                "eval_cost": "19547.00",
                "prefix_cost": "138442.33",
                "data_read_per_join": "711M"
              },
              "used_columns": [
                "id",
                "path"
              ]
            }
          }
        ],
        "select_list_subqueries": [
          {
            "dependent": true,
            "cacheable": false,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "0.54"
              },
              "table": {
                "table_name": "cscart_product_stocks",
                "access_type": "ref",
                "possible_keys": [
                  "cscart_product_stocks_pk"
                ],
                "key": "cscart_product_stocks_pk",
                "used_key_parts": [
                  "product_id"
                ],
                "key_length": "4",
                "ref": [
                  "func"
                ],
                "rows_examined_per_scan": 1,
                "rows_produced_per_join": 1,
                "filtered": "100.00",
                "using_index": true,
                "cost_info": {
                  "read_cost": "0.44",
                  "eval_cost": "0.10",
                  "prefix_cost": "0.54",
                  "data_read_per_join": "24"
                },
                "used_columns": [
                  "product_id"
                ],
                "attached_condition": "(`dev2`.`cscart_product_stocks`.`product_id` = `dev2`.`P`.`product_id`)"
              }
            }
          }
        ]
      }
    }
  }
}