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 (
    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.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 Vicious-Circle-Recordings-331-Artwork.jpg 3000 3000 95797 92933 product 99320 M 0 99320
0 DS036.jpg 4000 4000 100704 97665 product 104227 M 0 104227
0 5054286061489.jpg 1000 1000 100925 97905 product 104448 M 0 104448
0 5054285775974.jpg 1000 1000 100998 97978 product 104521 M 0 104521
0 5054285775974_6xkz-78.jpg 1000 1000 100999 97979 product 104522 M 0 104522
0 5054285775974_rx57-m2.jpg 1000 1000 101000 97980 product 104523 M 0 104523
0 5054285775974_equv-sa.jpg 1000 1000 101001 97981 product 104524 M 0 104524
0 5054286072539.jpg 1000 1000 101094 98075 product 104617 M 0 104617
0 RRR027.jpg 1500 1500 101201 89237 product 104724 M 0 104724
0 DFB_9e1s-jz.jpg 1500 1500 101509 98473 product 105032 M 0 105032
0 DFB_i3gr-pp.jpg 1500 1500 101510 98474 product 105033 M 0 105033
0 DFB_b4be-4e.jpg 1500 1500 101511 98475 product 105034 M 0 105034
0 DFB_qjwq-4f.jpg 1500 1500 101512 98476 product 105035 M 0 105035
0 TBHH035b.jpeg 3000 3000 101513 98477 product 105036 M 0 105036
0 IA017_Fun_At_The_Hoover_Fair.jpg 1600 1600 101514 98478 product 105037 M 0 105037
0 50.jpg 800 800 101515 98479 product 105038 M 0 105038
0 Zillin_-_Manson_sleeve.jpg 1080 1080 101516 98480 product 105039 M 0 105039
0 50_ftxk-tn.jpg 800 800 101517 98481 product 105040 M 0 105040
0 Underground_Sounds__Hayz_Remix_.png 2000 2000 101554 98518 product 105077 M 0 105077
0 cover.1.jpg 400 400 101558 98522 product 105081 M 0 105081