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 (
    10012, 718, 9967, 9844, 710, 720, 9931, 
    9840, 719, 715, 10946, 9964
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00091

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 16 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
13566 9840 product 0 9153 A 0
25566 718 product 0 21141 A 0
25567 718 product 0 21142 A 0
25571 9964 product 0 21146 A 0
28153 715 product 0 23728 A 0
28162 719 product 0 23737 A 0
28165 715 product 0 23740 A 0
28166 715 product 0 23741 A 0
28167 715 product 0 23742 A 0
28168 715 product 0 23743 A 0
28169 715 product 0 23744 A 0
28170 715 product 0 23745 A 0
30479 9844 product 0 26056 A 0
30480 9844 product 0 26057 A 0
30481 9844 product 0 26058 A 0
30482 9844 product 0 26059 A 0
30483 9844 product 0 26060 A 0
31218 710 product 0 26796 A 0
31219 710 product 0 26797 A 0
31220 710 product 0 26798 A 0
31231 720 product 0 26809 A 0
31232 720 product 0 26810 A 0
31511 10012 product 0 27089 A 0
31532 9931 product 0 27110 A 0
31229 719 product 0 26807 A 1
31512 10012 product 0 27090 A 1
28163 719 product 0 23738 A 2
31513 10012 product 0 27091 A 2
31230 719 product 0 26808 A 3
31514 10012 product 0 27092 A 3
28164 719 product 0 23739 A 4
31516 10012 product 0 27094 A 4
31515 10012 product 0 27093 A 5
27845 10012 product 0 23420 A 6
27843 10012 product 0 23418 A 7