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 (
    100653, 100652, 100651, 100650, 100649, 
    101334, 101333, 101332, 101331, 101330, 
    101329, 101328, 101327, 101326, 101325, 
    101324, 101323, 101322, 101321, 101320
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00072

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 103731 100649 product 107254 M 0
0 103732 100650 product 107255 M 0
0 103733 100651 product 107256 M 0
0 103734 100652 product 107257 M 0
0 103735 100653 product 107258 M 0
0 104403 101320 product 107926 M 0
0 104404 101321 product 107927 M 0
0 104405 101322 product 107928 M 0
0 104406 101323 product 107929 M 0
0 104407 101324 product 107930 M 0
0 104408 101325 product 107931 M 0
0 104409 101326 product 107932 M 0
0 104410 101327 product 107933 M 0
0 104411 101328 product 107934 M 0
0 104412 101329 product 107935 M 0
0 104413 101330 product 107936 M 0
0 104414 101331 product 107937 M 0
0 104415 101332 product 107938 M 0
0 104416 101333 product 107939 M 0
0 104417 101334 product 107940 M 0