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.detailed_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 (
    43849, 43758, 43767, 43766, 43737, 43620, 
    43567, 43566, 43648, 43512, 43400, 
    43399, 43398, 43397, 43430, 43429, 
    43428, 43427, 43426, 43237
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00064

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.detailed_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 5054281745995.jpg 1000 1000 44745 43237 product 48268 M 0 48268
0 5054281755949.jpg 1000 1000 44903 43397 product 48426 M 0 48426
0 5054281755949_wr4d-m4.jpg 1000 1000 44904 43398 product 48427 M 0 48427
0 5054281755949_78l4-42.jpg 1000 1000 44905 43399 product 48428 M 0 48428
0 5054281755949_zihw-al.jpg 1000 1000 44906 43400 product 48429 M 0 48429
0 5054281758124.jpg 1000 1000 44932 43426 product 48455 M 0 48455
0 5054281758124_iiip-yv.jpg 1000 1000 44933 43427 product 48456 M 0 48456
0 5054281758124_r7pw-c4.jpg 1000 1000 44934 43428 product 48457 M 0 48457
0 5054281758124_acgt-uz.jpg 1000 1000 44935 43429 product 48458 M 0 48458
0 5054281758124_5h0x-oq.jpg 1000 1000 44936 43430 product 48459 M 0 48459
0 5054281760240_kbnt-is.jpg 1000 1000 45019 43512 product 48542 M 0 48542
0 5054281768796.jpg 1000 1000 45073 43566 product 48596 M 0 48596
0 5054281768796_7cp8-38.jpg 1000 1000 45074 43567 product 48597 M 0 48597
0 5054281771253.jpg 1000 1000 45128 43620 product 48651 M 0 48651
0 5054281774179.jpg 1000 1000 45156 43648 product 48679 M 0 48679
0 5054281778849.jpg 1000 1000 45242 43737 product 48765 M 0 48765
0 5054281782167.jpg 1000 1000 45266 43758 product 48789 M 0 48789
0 5054281781146.jpg 1000 1000 45274 43766 product 48797 M 0 48797
0 5054281782242.jpg 1000 1000 45275 43767 product 48798 M 0 48798
0 5054281786356.jpg 1000 1000 45357 43849 product 48880 M 0 48880