SELECT 
  cscart_images_links.*, 
  cscart_images.image_path, 
  cscart_common_descriptions.description AS alt, 
  cscart_images.image_x, 
  cscart_images.image_y, 
  cscart_images.image_id as images_image_id 
FROM 
  cscart_images_links 
  LEFT JOIN cscart_images ON cscart_images_links.image_id = cscart_images.image_id 
  LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id 
  AND cscart_common_descriptions.object_holder = 'images' 
  AND cscart_common_descriptions.lang_code = 'ro' 
WHERE 
  cscart_images_links.object_type = 'product' 
  AND cscart_images_links.type = 'M' 
  AND cscart_images_links.object_id IN (
    1662418, 1659922, 1661347, 1657700, 
    1658556, 1659797, 1659562, 1657765, 
    1657745, 1661374, 1915737, 1659943, 
    1661339, 1659257, 1660558, 1659816, 
    2591113, 1658321, 1662787, 1664138, 
    1660123, 1664236, 1661363, 1916044, 
    1660260, 1657702, 1660929, 1663152, 
    2596602, 2099753, 1658439, 1663290, 
    1661128, 1658521, 1658562, 2068216
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.35745

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "191.36"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "36.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "cscart_images_links",
            "access_type": "range",
            "possible_keys": [
              "object_id"
            ],
            "key": "object_id",
            "used_key_parts": [
              "object_id",
              "object_type",
              "type"
            ],
            "key_length": "81",
            "rows_examined_per_scan": 36,
            "rows_produced_per_join": 36,
            "filtered": "100.00",
            "index_condition": "((`maniamall_cscart`.`cscart_images_links`.`object_type` = 'product') and (`maniamall_cscart`.`cscart_images_links`.`type` = 'M') and (`maniamall_cscart`.`cscart_images_links`.`object_id` in (1662418,1659922,1661347,1657700,1658556,1659797,1659562,1657765,1657745,1661374,1915737,1659943,1661339,1659257,1660558,1659816,2591113,1658321,1662787,1664138,1660123,1664236,1661363,1916044,1660260,1657702,1660929,1663152,2596602,2099753,1658439,1663290,1661128,1658521,1658562,2068216)))",
            "cost_info": {
              "read_cost": "72.86",
              "eval_cost": "3.60",
              "prefix_cost": "76.46",
              "data_read_per_join": "3K"
            },
            "used_columns": [
              "pair_id",
              "object_id",
              "object_type",
              "image_id",
              "detailed_id",
              "type",
              "position"
            ]
          }
        },
        {
          "table": {
            "table_name": "cscart_images",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "image_id"
            ],
            "key_length": "3",
            "ref": [
              "maniamall_cscart.cscart_images_links.image_id"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 36,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "35.93",
              "eval_cost": "3.60",
              "prefix_cost": "115.99",
              "data_read_per_join": "27K"
            },
            "used_columns": [
              "image_id",
              "image_path",
              "image_x",
              "image_y"
            ],
            "attached_condition": "<if>(is_not_null_compl(cscart_images), (`maniamall_cscart`.`cscart_images_links`.`image_id` = `maniamall_cscart`.`cscart_images`.`image_id`), true)"
          }
        },
        {
          "table": {
            "table_name": "cscart_common_descriptions",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "object_id",
              "lang_code",
              "object_holder"
            ],
            "key_length": "107",
            "ref": [
              "maniamall_cscart.cscart_images.image_id",
              "const",
              "const"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 36,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "35.77",
              "eval_cost": "3.60",
              "prefix_cost": "155.37",
              "data_read_per_join": "21K"
            },
            "used_columns": [
              "object_id",
              "description",
              "lang_code",
              "object_holder"
            ],
            "attached_condition": "<if>(is_not_null_compl(cscart_common_descriptions), (`maniamall_cscart`.`cscart_common_descriptions`.`object_id` = `maniamall_cscart`.`cscart_images`.`image_id`), true)"
          }
        }
      ]
    }
  }
}

Result

pair_id object_id object_type image_id detailed_id type position image_path alt image_x image_y images_image_id
3642788 1657700 product 0 3643041 M 0
3642790 1657702 product 0 3643043 M 0
3642843 1657745 product 0 3643096 M 0
3642865 1657765 product 0 3643118 M 0
3643566 1658321 product 0 3643819 M 0
3643689 1658439 product 0 3643942 M 0
3643775 1658521 product 0 3644028 M 0
3643810 1658556 product 0 3644063 M 0
3643816 1658562 product 0 3644069 M 0
3644548 1659257 product 0 3644801 M 0
3644867 1659562 product 0 3645120 M 0
3645106 1659797 product 0 3645359 M 0
3645126 1659816 product 0 3645379 M 0
3645233 1659922 product 0 3645486 M 0
3645255 1659943 product 0 3645508 M 0
3645442 1660123 product 0 3645695 M 0
3645586 1660260 product 0 3645839 M 0
3645884 1660558 product 0 3646137 M 0
3646270 1660929 product 0 3646523 M 0
3646472 1661128 product 0 3646725 M 0
3646686 1661339 product 0 3646939 M 0
3646694 1661347 product 0 3646947 M 0
3646710 1661363 product 0 3646963 M 0
3646721 1661374 product 0 3646974 M 0
3647769 1662418 product 0 3648022 M 0
3648129 1662787 product 0 3648382 M 0
3648494 1663152 product 0 3648747 M 0
3648632 1663290 product 0 3648885 M 0
3649488 1664138 product 0 3649741 M 0
3649589 1664236 product 0 3649842 M 0
4629285 1915737 product 0 4629538 M 0
4629597 1916044 product 0 4629850 M 0
4962657 2068216 product 0 4962910 M 0
5161633 2099753 product 0 5161886 M 0
6707879 2591113 product 0 6708132 M 0
6723838 2596602 product 0 6724091 M 0