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 (
    101358, 101703, 101984, 101983, 101944, 
    101921, 101842, 101841, 101839, 101838, 
    101837, 101836, 101835, 101834, 101833, 
    101832, 101831, 101830, 101829, 101828
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00047

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 104441 101358 product 107964 M 0
0 104794 101703 product 108317 M 0
0 104918 101828 product 108441 M 0
0 104919 101829 product 108442 M 0
0 104920 101830 product 108443 M 0
0 104921 101831 product 108444 M 0
0 104922 101832 product 108445 M 0
0 104923 101833 product 108446 M 0
0 104924 101834 product 108447 M 0
0 104925 101835 product 108448 M 0
0 104926 101836 product 108449 M 0
0 104927 101837 product 108450 M 0
0 104928 101838 product 108451 M 0
0 104929 101839 product 108452 M 0
0 104931 101841 product 108454 M 0
0 104932 101842 product 108455 M 0
0 105007 101921 product 108530 M 0
0 105031 101944 product 108554 M 0
0 105070 101983 product 108593 M 0
0 105071 101984 product 108594 M 0