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 (
    386230, 1026757, 671515, 1001748, 1002989, 
    1026639, 1026634, 1028815, 1029088, 
    459108, 562176, 1205512, 1779540, 
    1029029, 1914195, 1028861, 1006064, 
    1329210, 999679, 1005469, 1026067, 
    1005477, 1028489, 1001621, 1000770, 
    1051465, 1026826, 1051466, 1000273, 
    1000893, 1005777, 999510, 998713, 
    1026835, 1005493, 1000638, 1118393, 
    999450, 999451, 1914203, 2089921, 
    1002439, 1914202, 1033401, 2077210, 
    1730378, 1730377, 1033409, 1730386, 
    1730384, 999697, 1734632, 1629494, 
    1232017, 1232162, 1231209, 1231096, 
    1629497, 2068214, 1231338, 1232051, 
    1232167, 1231215, 1232054, 1231839, 
    1162749, 1231181, 1232163, 1221302, 
    1162927, 1231233, 1162511, 1231645, 
    1231046, 1231908, 1877564, 1162375, 
    1231628, 1231180, 1283561, 1444141, 
    1232095, 1231907, 1162915, 1231723, 
    1231626, 1162423, 1231072, 1629452, 
    1231559, 1129584, 1221276, 1162412, 
    1221274, 1915661, 2659440
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00536

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "490.89"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "96.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": 96,
            "rows_produced_per_join": 96,
            "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 (386230,1026757,671515,1001748,1002989,1026639,1026634,1028815,1029088,459108,562176,1205512,1779540,1029029,1914195,1028861,1006064,1329210,999679,1005469,1026067,1005477,1028489,1001621,1000770,1051465,1026826,1051466,1000273,1000893,1005777,999510,998713,1026835,1005493,1000638,1118393,999450,999451,1914203,2089921,1002439,1914202,1033401,2077210,1730378,1730377,1033409,1730386,1730384,999697,1734632,1629494,1232017,1232162,1231209,1231096,1629497,2068214,1231338,1232051,1232167,1231215,1232054,1231839,1162749,1231181,1232163,1221302,1162927,1231233,1162511,1231645,1231046,1231908,1877564,1162375,1231628,1231180,1283561,1444141,1232095,1231907,1162915,1231723,1231626,1162423,1231072,1629452,1231559,1129584,1221276,1162412,1221274,1915661,2659440)))",
            "cost_info": {
              "read_cost": "179.95",
              "eval_cost": "9.60",
              "prefix_cost": "189.55",
              "data_read_per_join": "9K"
            },
            "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": 96,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "94.23",
              "eval_cost": "9.60",
              "prefix_cost": "293.38",
              "data_read_per_join": "73K"
            },
            "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": 96,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "91.91",
              "eval_cost": "9.60",
              "prefix_cost": "394.89",
              "data_read_per_join": "57K"
            },
            "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