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 (
    2221, 2228, 2181, 2220, 2412, 2222, 2223, 
    2585, 2576, 2578, 2577, 2575
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00106

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": 50,
          "filtered": 100,
          "index_condition": "cscart_images_links.object_type = 'product' and cscart_images_links.`type` = 'A' and cscart_images_links.object_id in (2221,2228,2181,2220,2412,2222,2223,2585,2576,2578,2577,2575)"
        }
      }
    },
    "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 9976 2181 product 8186 A 1
0 9978 2221 product 8188 A 1
0 9979 2222 product 8189 A 1
0 9980 2223 product 8190 A 1
0 9984 2228 product 8194 A 1
0 10009 2576 product 8219 A 1
0 10017 2577 product 8227 A 1
0 10025 2578 product 8235 A 1
0 10104 2585 product 8314 A 1
0 7577 2181 product 5787 A 2
0 7619 2221 product 5829 A 2
0 7633 2222 product 5843 A 2
0 7647 2223 product 5857 A 2
0 7709 2228 product 5919 A 2
0 9991 2412 product 8201 A 2
0 10000 2575 product 8210 A 2
0 10010 2576 product 8220 A 2
0 10018 2577 product 8228 A 2
0 10026 2578 product 8236 A 2
0 10049 2220 product 8259 A 2
0 10105 2585 product 8315 A 2
0 7578 2181 product 5788 A 3
0 7620 2221 product 5830 A 3
0 7634 2222 product 5844 A 3
0 7648 2223 product 5858 A 3
0 7710 2228 product 5920 A 3
0 8887 2412 product 7097 A 3
0 10001 2575 product 8211 A 3
0 10011 2576 product 8221 A 3
0 10019 2577 product 8229 A 3
0 10027 2578 product 8237 A 3
0 10050 2220 product 8260 A 3
0 10106 2585 product 8316 A 3
0 7579 2181 product 5789 A 4
0 7621 2221 product 5831 A 4
0 7635 2222 product 5845 A 4
0 7649 2223 product 5859 A 4
0 7711 2228 product 5921 A 4
0 8888 2412 product 7098 A 4
0 10002 2575 product 8212 A 4
0 10012 2576 product 8222 A 4
0 10020 2577 product 8230 A 4
0 10028 2578 product 8238 A 4
0 10051 2220 product 8261 A 4
0 10107 2585 product 8317 A 4
0 7580 2181 product 5790 A 5
0 7607 2220 product 5817 A 5
0 7622 2221 product 5832 A 5
0 7636 2222 product 5846 A 5
0 7650 2223 product 5860 A 5
0 7712 2228 product 5922 A 5
0 8889 2412 product 7099 A 5
0 10003 2575 product 8213 A 5
0 10013 2576 product 8223 A 5
0 10021 2577 product 8231 A 5
0 10029 2578 product 8239 A 5
0 10108 2585 product 8318 A 5
0 7581 2181 product 5791 A 6
0 7608 2220 product 5818 A 6
0 7623 2221 product 5833 A 6
0 7637 2222 product 5847 A 6
0 7651 2223 product 5861 A 6
0 7713 2228 product 5923 A 6
0 8890 2412 product 7100 A 6
0 10004 2575 product 8214 A 6
0 10014 2576 product 8224 A 6
0 10022 2577 product 8232 A 6
0 10030 2578 product 8240 A 6
0 10109 2585 product 8319 A 6
0 7582 2181 product 5792 A 7
0 7609 2220 product 5819 A 7
0 7624 2221 product 5834 A 7
0 7638 2222 product 5848 A 7
0 7652 2223 product 5862 A 7
0 7714 2228 product 5924 A 7
0 8891 2412 product 7101 A 7
0 10005 2575 product 8215 A 7
0 10023 2577 product 8233 A 7
0 10031 2578 product 8241 A 7
0 10110 2585 product 8320 A 7
0 10291 2576 product 8501 A 7
0 7610 2220 product 5820 A 8
0 8892 2412 product 7102 A 8
0 10006 2575 product 8216 A 8
0 10111 2585 product 8321 A 8