SELECT 
  cscart_images.*, 
  cscart_images_links.*, 
  cscart_common_descriptions.description AS alt, 
  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 = 'ru' 
WHERE 
  cscart_images_links.object_type = 'product' 
  AND cscart_images_links.type = 'A' 
  AND cscart_images_links.object_id IN (
    1540, 2082, 2404, 1516, 2405, 1545, 352, 
    2092, 2400, 366, 1517, 356
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00070

JSON explain

{
  "query_block": {
    "select_id": 1,
    "read_sorted_file": {
      "filesort": {
        "sort_key": "cscart_images_links.position, cscart_images_links.pair_id",
        "table": {
          "table_name": "cscart_images_links",
          "access_type": "range",
          "possible_keys": ["object_id"],
          "key": "object_id",
          "key_length": "81",
          "used_key_parts": ["object_id", "object_type", "type"],
          "rows": 49,
          "filtered": 100,
          "index_condition": "cscart_images_links.object_type = 'product' and cscart_images_links.`type` = 'A' and cscart_images_links.object_id in (1540,2082,2404,1516,2405,1545,352,2092,2400,366,1517,356)"
        }
      }
    },
    "table": {
      "table_name": "cscart_images",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["image_id"],
      "ref": ["noframes_kz_new.cscart_images_links.image_id"],
      "rows": 1,
      "filtered": 100
    },
    "table": {
      "table_name": "cscart_common_descriptions",
      "access_type": "eq_ref",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "107",
      "used_key_parts": ["object_id", "lang_code", "object_holder"],
      "ref": ["noframes_kz_new.cscart_images.image_id", "const", "const"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "trigcond(cscart_common_descriptions.object_id = cscart_images.image_id and cscart_common_descriptions.object_holder = 'images' and cscart_common_descriptions.lang_code = 'ru' and trigcond(cscart_images.image_id is not null))"
    }
  }
}

Result

image_id image_path image_x image_y is_high_res rf_original pair_id object_id object_type detailed_id type position alt images_image_id
0 10782 1545 product 8989 A 1
0 10976 2400 product 9183 A 1
0 10996 2405 product 9203 A 2
0 11897 2400 product 10104 A 2
0 11898 2400 product 10105 A 3
0 10977 2400 product 9184 A 4
0 10991 2404 product 9198 A 4
0 10978 2400 product 9185 A 5
0 10992 2404 product 9199 A 5
0 10997 2405 product 9204 A 5
0 10732 366 product 8939 A 6
0 10875 1540 product 9082 A 6
0 10979 2400 product 9186 A 6
0 10993 2404 product 9200 A 6
0 10998 2405 product 9205 A 6
0 10702 1517 product 8909 A 7
0 10726 2082 product 8933 A 7
0 10733 366 product 8940 A 7
0 10762 1516 product 8969 A 7
0 10768 2092 product 8975 A 7
0 10783 1545 product 8990 A 7
0 10876 1540 product 9083 A 7
0 10994 2404 product 9201 A 7
0 10999 2405 product 9206 A 7
0 10703 1517 product 8910 A 8
0 10727 2082 product 8934 A 8
0 10734 366 product 8941 A 8
0 10763 1516 product 8970 A 8
0 10769 2092 product 8976 A 8
0 10784 1545 product 8991 A 8
0 10790 352 product 8997 A 8
0 10803 356 product 9010 A 8
0 10877 1540 product 9084 A 8
0 11000 2405 product 9207 A 8
0 10704 1517 product 8911 A 9
0 10728 2082 product 8935 A 9
0 10764 1516 product 8971 A 9
0 10770 2092 product 8977 A 9
0 10791 352 product 8998 A 9
0 10804 356 product 9011 A 9
0 10878 1540 product 9085 A 9
0 11001 2405 product 9208 A 9
0 10705 1517 product 8912 A 10
0 10729 2082 product 8936 A 10
0 10765 1516 product 8972 A 10
0 10771 2092 product 8978 A 10
0 10792 352 product 8999 A 10
0 10805 356 product 9012 A 10
0 10706 1517 product 8913 A 11
0 10730 2082 product 8937 A 11
0 10766 1516 product 8973 A 11
0 10772 2092 product 8979 A 11
0 10793 352 product 9000 A 11
0 10806 356 product 9013 A 11
0 10773 2092 product 8980 A 12