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 = 'en' 
WHERE 
  cscart_images_links.object_type = 'product' 
  AND cscart_images_links.type = 'M' 
  AND cscart_images_links.object_id IN (
    39037, 39033, 39028, 16065, 16030, 15873, 
    14556, 15818, 15707, 15625, 15692, 
    15689, 15688, 15340, 15531, 15288, 
    14901, 14869, 39048, 14806
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00040

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 20 Using index condition; Using filesort
1 SIMPLE cscart_images eq_ref PRIMARY PRIMARY 4 toolboxdigitalshop_dev.cscart_images_links.image_id 1
1 SIMPLE cscart_common_descriptions eq_ref PRIMARY PRIMARY 108 toolboxdigitalshop_dev.cscart_images.image_id,const,const 1 Using where

Result

image_id image_path image_x image_y pair_id object_id object_type detailed_id type position alt images_image_id
0 14569 14556 product 18092 M 0
0 14818 14806 product 18341 M 0
0 14883 14869 product 18406 M 0
0 14915 14901 product 18438 M 0
0 15302 15288 product 18825 M 0
0 15354 15340 product 18877 M 0
0 15544 15531 product 19067 M 0
0 15639 15625 product 19162 M 0
0 15702 15688 product 19225 M 0
0 15703 15689 product 19226 M 0
0 15706 15692 product 19229 M 0
0 15721 15707 product 19244 M 0
0 15832 15818 product 19355 M 0
0 15887 15873 product 19410 M 0
0 16044 16030 product 19567 M 0
0 16079 16065 product 19602 M 0
0 40488 39028 product 44011 M 0
0 40493 39033 product 44016 M 0
0 40497 39037 product 44020 M 0
0 40508 39048 product 44031 M 0