SELECT 
  DISTINCT `PC`.`category_id`, 
  `CD`.`category`, 
  `C`.`level`, 
  `C`.`parent_id`, 
  `C`.`id_path` 
FROM 
  `cscart_products` AS `P` 
  JOIN `cscart_products_categories` AS `PC` ON `P`.`product_id` = `PC`.`product_id` 
  LEFT JOIN `cscart_category_descriptions` AS `CD` ON `CD`.`category_id` = `PC`.`category_id` 
  LEFT JOIN `cscart_categories` AS `C` ON `C`.`category_id` = `PC`.`category_id` 
WHERE 
  `P`.`company_id` = 692 
ORDER BY 
  `CD`.`category` ASC

Query time 0.00998

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "6211.06"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "duplicates_removal": {
        "using_temporary_table": true,
        "using_filesort": false,
        "cost_info": {
          "sort_cost": "1813.19"
        },
        "nested_loop": [
          {
            "table": {
              "table_name": "P",
              "access_type": "ALL",
              "possible_keys": [
                "PRIMARY",
                "cscart_products_product_id_index"
              ],
              "rows_examined_per_scan": 17394,
              "rows_produced_per_join": 1739,
              "filtered": "10.00",
              "cost_info": {
                "read_cost": "1669.71",
                "eval_cost": "173.94",
                "prefix_cost": "1843.65",
                "data_read_per_join": "12M"
              },
              "used_columns": [
                "product_id",
                "company_id"
              ],
              "attached_condition": "(`dev2`.`P`.`company_id` = 692)"
            }
          },
          {
            "table": {
              "table_name": "PC",
              "access_type": "ref",
              "possible_keys": [
                "pt",
                "idx_pc_product_id_type"
              ],
              "key": "pt",
              "used_key_parts": [
                "product_id"
              ],
              "key_length": "3",
              "ref": [
                "dev2.P.product_id"
              ],
              "rows_examined_per_scan": 1,
              "rows_produced_per_join": 1813,
              "filtered": "100.00",
              "using_index": true,
              "cost_info": {
                "read_cost": "434.88",
                "eval_cost": "181.32",
                "prefix_cost": "2459.85",
                "data_read_per_join": "28K"
              },
              "used_columns": [
                "product_id",
                "category_id"
              ]
            }
          },
          {
            "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": 1813,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "453.30",
                "eval_cost": "181.32",
                "prefix_cost": "3094.46",
                "data_read_per_join": "7M"
              },
              "used_columns": [
                "category_id",
                "lang_code",
                "category"
              ]
            }
          },
          {
            "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": 1813,
              "filtered": "100.00",
              "cost_info": {
                "read_cost": "1122.09",
                "eval_cost": "181.32",
                "prefix_cost": "4397.87",
                "data_read_per_join": "19M"
              },
              "used_columns": [
                "category_id",
                "parent_id",
                "id_path",
                "level"
              ]
            }
          }
        ]
      }
    }
  }
}