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 `P`.`status` = 'A' AND `P`.`company_id` IN (692) AND `COM`.`status` = 'A' GROUP BY `P`.`product_id` ORDER BY city_priority DESC,updated_timestamp DESC LIMIT 0, 12

Query is invalid

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "20065046.33"
    },
    "ordering_operation": {
      "using_filesort": true,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "nested_loop": [
          {
            "table": {
              "table_name": "COM",
              "access_type": "const",
              "possible_keys": [
                "PRIMARY"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "company_id"
              ],
              "key_length": "4",
              "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": "7K"
              },
              "used_columns": [
                "company_id",
                "status",
                "company"
              ]
            }
          },
          {
            "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"
              ],
              "key_length": "3",
              "ref": [
                "const"
              ],
              "rows_examined_per_scan": 9252,
              "rows_produced_per_join": 9252,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "2.79",
                "eval_cost": "925.20",
                "prefix_cost": "927.99",
                "data_read_per_join": "144K"
              },
              "used_columns": [
                "product_id",
                "category_id",
                "link_type"
              ]
            }
          },
          {
            "table": {
              "table_name": "C",
              "access_type": "eq_ref",
              "possible_keys": [
                "PRIMARY",
                "p_category_id"
              ],
              "key": "PRIMARY",
              "used_key_parts": [
                "category_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.PC.category_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 9252,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "5725.62",
                "eval_cost": "925.20",
                "prefix_cost": "7578.81",
                "data_read_per_join": "98M"
              },
              "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": [
                "dev2.PC.category_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 9252,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "2313.00",
                "eval_cost": "925.20",
                "prefix_cost": "10817.01",
                "data_read_per_join": "38M"
              },
              "used_columns": [
                "category_id",
                "lang_code",
                "category"
              ]
            }
          },
          {
            "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": 668,
              "filtered": "7.22",
              "cost_info": {
                "read_cost": "2313.00",
                "eval_cost": "66.82",
                "prefix_cost": "14055.21",
                "data_read_per_join": "4M"
              },
              "used_columns": [
                "product_id",
                "product_code",
                "status",
                "company_id",
                "amount",
                "timestamp",
                "updated_timestamp",
                "guarantee_id",
                "conditions",
                "year",
                "city_place",
                "city_id",
                "brand_id",
                "uniqueSale",
                "is_confiscated",
                "type_of_sale"
              ],
              "attached_condition": "((`dev2`.`P`.`company_id` = 692) and (`dev2`.`P`.`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": 668,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "187.94",
                "eval_cost": "66.82",
                "prefix_cost": "14309.98",
                "data_read_per_join": "161K"
              },
              "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": 668,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "556.83",
                "eval_cost": "66.82",
                "prefix_cost": "14933.63",
                "data_read_per_join": "2M"
              },
              "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": 668,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "167.06",
                "eval_cost": "66.82",
                "prefix_cost": "15167.51",
                "data_read_per_join": "62K"
              },
              "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": 668,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "167.06",
                "eval_cost": "66.82",
                "prefix_cost": "15401.39",
                "data_read_per_join": "2M"
              },
              "used_columns": [
                "brand_id",
                "brand",
                "has_subsidy"
              ]
            }
          },
          {
            "table": {
              "table_name": "GW",
              "access_type": "ALL",
              "rows_examined_per_scan": 48,
              "rows_produced_per_join": 32075,
              "filtered": "100.00",
              "using_join_buffer": "hash join",
              "cost_info": {
                "read_cost": "1.93",
                "eval_cost": "3207.53",
                "prefix_cost": "18610.85",
                "data_read_per_join": "501K"
              },
              "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": 2822626,
              "filtered": "100.00",
              "using_join_buffer": "hash join",
              "cost_info": {
                "read_cost": "81.43",
                "eval_cost": "282262.66",
                "prefix_cost": "300954.95",
                "data_read_per_join": "43M"
              },
              "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": 2903721,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "1279074.30",
                "eval_cost": "290372.18",
                "prefix_cost": "1870401.42",
                "data_read_per_join": "66M"
              },
              "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": 2903721,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "171.86",
                "eval_cost": "290372.18",
                "prefix_cost": "2160945.46",
                "data_read_per_join": "1G"
              },
              "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": 2903721,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "725930.44",
                "eval_cost": "290372.18",
                "prefix_cost": "3177248.07",
                "data_read_per_join": "14G"
              },
              "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": 7190041,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "3451219.70",
                "eval_cost": "719004.10",
                "prefix_cost": "7347471.87",
                "data_read_per_join": "1G"
              },
              "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": 28257631,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "7064407.86",
                "eval_cost": "2825763.14",
                "prefix_cost": "17237642.88",
                "data_read_per_join": "862M"
              },
              "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": 28257631,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1640.31",
                "eval_cost": "2825763.14",
                "prefix_cost": "20065046.33",
                "data_read_per_join": "100G"
              },
              "used_columns": [
                "id",
                "path"
              ]
            }
          }
        ],
        "select_list_subqueries": [
          {
            "dependent": true,
            "cacheable": false,
            "query_block": {
              "select_id": 2,
              "cost_info": {
                "query_cost": "0.56"
              },
              "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.45",
                  "eval_cost": "0.10",
                  "prefix_cost": "0.56",
                  "data_read_per_join": "24"
                },
                "used_columns": [
                  "product_id"
                ],
                "attached_condition": "(`dev2`.`cscart_product_stocks`.`product_id` = `dev2`.`P`.`product_id`)"
              }
            }
          }
        ]
      }
    }
  }
}