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 (
    515, 8686, 9862, 9863, 10125, 10124, 
    9859, 9858, 10384, 9857
  ) 
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 26 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
4127 515 product 0 3097 A 0
11634 8686 product 0 8127 A 0
28360 9859 product 0 23935 A 0
28362 9858 product 0 23937 A 0
28368 10124 product 0 23943 A 0
28375 9863 product 0 23950 A 0
28377 10125 product 0 23952 A 0
28384 9862 product 0 23959 A 0
28388 9857 product 0 23963 A 0
29522 10384 product 0 25099 A 0
29531 10384 product 0 25108 A 0
29532 10384 product 0 25109 A 0
29533 10384 product 0 25110 A 0
30029 10384 product 0 25606 A 0
28356 9859 product 0 23931 A 1
28366 9858 product 0 23941 A 1
28370 10124 product 0 23945 A 1
28374 9863 product 0 23949 A 1
28379 10125 product 0 23954 A 1
28385 9862 product 0 23960 A 1
28392 9857 product 0 23967 A 1
28396 9863 product 0 23971 A 1
28397 9863 product 0 23972 A 1
28398 9863 product 0 23973 A 1
30594 8686 product 0 26171 A 1
30595 515 product 0 26172 A 1
28358 9859 product 0 23933 A 2
28363 9858 product 0 23938 A 2
28369 10124 product 0 23944 A 2
28378 10125 product 0 23953 A 2
28383 9862 product 0 23958 A 2
28389 9857 product 0 23964 A 2
28393 9862 product 0 23968 A 2
28394 9862 product 0 23969 A 2
28395 9862 product 0 23970 A 2
30596 8686 product 0 26173 A 2
30603 515 product 0 26180 A 2
13312 8686 product 0 8899 A 3
27995 515 product 0 23570 A 3
28357 9859 product 0 23932 A 3
28364 9858 product 0 23939 A 3
28372 10124 product 0 23947 A 3
28381 10125 product 0 23956 A 3
28390 9857 product 0 23965 A 3
27610 8686 product 0 23185 A 4
27996 515 product 0 23571 A 4
28359 9859 product 0 23934 A 4
28365 9858 product 0 23940 A 4
28371 10124 product 0 23946 A 4
28380 10125 product 0 23955 A 4
28391 9857 product 0 23966 A 4
28399 10125 product 0 23974 A 4
28400 10125 product 0 23975 A 4
28401 10125 product 0 23976 A 4
28402 9857 product 0 23977 A 4
28403 9857 product 0 23978 A 4
28404 9857 product 0 23979 A 4
28405 9857 product 0 23980 A 4
28406 9858 product 0 23981 A 4
28407 9858 product 0 23982 A 4
28408 9858 product 0 23983 A 4
28409 9858 product 0 23984 A 4
25979 515 product 0 21554 A 5
25986 8686 product 0 21561 A 5
28410 9859 product 0 23985 A 5
28412 9859 product 0 23987 A 6
28411 9859 product 0 23986 A 7