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 (
    97981, 97980, 97979, 97978, 97665, 92933, 
    98075, 89237, 98522, 98518, 97905, 
    98481, 98480, 98479, 98478, 98477, 
    98476, 98475, 98474, 98473
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00050

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 95797 92933 product 99320 M 0
0 100704 97665 product 104227 M 0
0 100925 97905 product 104448 M 0
0 100998 97978 product 104521 M 0
0 100999 97979 product 104522 M 0
0 101000 97980 product 104523 M 0
0 101001 97981 product 104524 M 0
0 101094 98075 product 104617 M 0
0 101201 89237 product 104724 M 0
0 101509 98473 product 105032 M 0
0 101510 98474 product 105033 M 0
0 101511 98475 product 105034 M 0
0 101512 98476 product 105035 M 0
0 101513 98477 product 105036 M 0
0 101514 98478 product 105037 M 0
0 101515 98479 product 105038 M 0
0 101516 98480 product 105039 M 0
0 101517 98481 product 105040 M 0
0 101554 98518 product 105077 M 0
0 101558 98522 product 105081 M 0