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 (
    97173, 97172, 97030, 97023, 96557, 94775, 
    92923, 96735, 97037, 96932, 97022, 
    96993, 96992, 96991, 96986, 96933, 
    96890, 96183, 92922, 96734
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00045

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 95786 92922 product 99309 M 0
0 95787 92923 product 99310 M 0
0 97663 94775 product 101186 M 0
0 99159 96183 product 102682 M 0
0 99534 96557 product 103057 M 0
0 99715 96734 product 103238 M 0
0 99716 96735 product 103239 M 0
0 99872 96890 product 103395 M 0
0 99915 96932 product 103438 M 0
0 99916 96933 product 103439 M 0
0 99969 96986 product 103492 M 0
0 99974 96991 product 103497 M 0
0 99975 96992 product 103498 M 0
0 99976 96993 product 103499 M 0
0 100006 97022 product 103529 M 0
0 100007 97023 product 103530 M 0
0 100016 97030 product 103539 M 0
0 100022 97037 product 103545 M 0
0 100178 97172 product 103701 M 0
0 100179 97173 product 103702 M 0