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 = 'en' 
WHERE 
  cscart_images_links.object_type = 'product' 
  AND cscart_images_links.type = 'A' 
  AND cscart_images_links.object_id IN (
    3316, 8888, 1310, 9821, 9965, 8877, 9823, 
    721, 10066, 10095, 1311, 10013
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00136

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_images_links range object_id object_id 81 28 Using index condition; Using filesort
1 SIMPLE cscart_images eq_ref PRIMARY PRIMARY 3 mahm3t_cs443.cscart_images_links.image_id 1 Using where
1 SIMPLE cscart_common_descriptions eq_ref PRIMARY PRIMARY 107 mahm3t_cs443.cscart_images.image_id,const,const 1 Using where

Result

pair_id object_id object_type image_id detailed_id type position emo_md5 image_path alt image_x image_y images_image_id
12528 8888 product 0 8441 A 0
12529 8888 product 0 8442 A 0
12530 8888 product 0 8443 A 0
12531 8888 product 0 8444 A 0
12532 8888 product 0 8445 A 0
12591 8877 product 0 8513 A 0
25570 9965 product 0 21145 A 0
26048 10095 product 0 21623 A 0
26049 10095 product 0 21624 A 0
26050 10095 product 0 21625 A 0
28160 721 product 0 23735 A 0
31183 1311 product 0 26761 A 0
31184 1311 product 0 26762 A 0
31185 1311 product 0 26763 A 0
31186 1311 product 0 26764 A 0
31187 1311 product 0 26765 A 0
31197 1310 product 0 26775 A 0
31198 1310 product 0 26776 A 0
31199 1310 product 0 26777 A 0
31233 721 product 0 26811 A 0
31234 721 product 0 26812 A 0
31235 721 product 0 26813 A 0
31358 10066 product 0 26936 A 0
31359 10066 product 0 26937 A 0
31360 10066 product 0 26938 A 0
31361 10066 product 0 26939 A 0
31362 10066 product 0 26940 A 0
31363 10066 product 0 26941 A 0
31517 10013 product 0 27095 A 0
31638 9821 product 0 27216 A 0
31639 9821 product 0 27217 A 0
31640 9821 product 0 27218 A 0
31641 9821 product 0 27219 A 0
31642 9821 product 0 27220 A 0
31643 9821 product 0 27221 A 0
31644 9821 product 0 27222 A 0
31645 9821 product 0 27223 A 0
31646 9821 product 0 27224 A 0
31647 9821 product 0 27225 A 0
31648 9821 product 0 27226 A 0
31650 9823 product 0 27228 A 0
31651 9823 product 0 27229 A 0
31652 9823 product 0 27230 A 0
31653 9823 product 0 27231 A 0
31654 9823 product 0 27232 A 0
31655 9823 product 0 27233 A 0
31656 9823 product 0 27234 A 0
31657 9823 product 0 27235 A 0
31658 9823 product 0 27236 A 0
31659 9823 product 0 27237 A 0
12593 8877 product 0 8515 A 1
28155 9965 product 0 23730 A 1
28159 721 product 0 23734 A 1
31518 10013 product 0 27096 A 1
12594 8877 product 0 8516 A 2
28158 9965 product 0 23733 A 2
28161 721 product 0 23736 A 2
31519 10013 product 0 27097 A 2
12595 8877 product 0 8517 A 3
28157 9965 product 0 23732 A 3
31520 10013 product 0 27098 A 3
12596 8877 product 0 8518 A 4
28156 9965 product 0 23731 A 4
31521 10013 product 0 27099 A 4
12597 8877 product 0 8519 A 5
27805 10013 product 0 23380 A 5
31261 9965 product 0 26839 A 5
12592 8877 product 0 8514 A 6
27807 10013 product 0 23382 A 6
27808 10013 product 0 23383 A 7