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 (
    97698, 97697, 97696, 97695, 97669, 97655, 
    97558, 97393, 96323, 96738, 89235, 
    97593, 97584, 97095, 97094, 97582, 
    97562, 97561, 97511, 94776
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00079

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 TBHH035a_Lo_Pan_-_Faith_Artwork_copy-min.jpg 3000 3000 99299 96323 product 102822 M 0 102822
0 St18_tears.jpg 1181 1181 99719 96738 product 103242 M 0 103242
0 5054285992326.jpg 1000 1000 100079 97094 product 103602 M 0 103602
0 5054285992326_7x3y-ye.jpg 1000 1000 100080 97095 product 103603 M 0 103603
0 Frantic_sleeve_David_morrish_love_is_blind_1MB__1_.jpg 3000 3000 100091 94776 product 103614 M 0 103614
0 tara.jpg 929 949 100401 97393 product 103924 M 0 103924
0 received_353098126415363.jpeg 2000 2000 100522 97511 product 104045 M 0 104045
0 WLHH028_Ben_Warren_-_Rooms_Hot.jpg 3600 3600 100569 97558 product 104092 M 0 104092
0 Not_Superior.jpg 1750 1750 100571 97561 product 104094 M 0 104094
0 BOSKI___BIZZAREQ_2.jpg 1600 1600 100572 97562 product 104095 M 0 104095
0 ESP_Turcois.png 400 400 100594 97584 product 104117 M 0 104117
0 Feelings.png 2000 2000 100603 97593 product 104126 M 0 104126
0 GARH002_clear_your_mind.png 2000 2000 100665 97655 product 104188 M 0 104188
0 TMP0180.png 2160 2160 100676 97669 product 104199 M 0 104199
0 RRR024.jpg 1500 1500 100678 89235 product 104201 M 0 104201
0 1.1.png 3000 3000 100712 97695 product 104235 M 0 104235
0 received_1005587740210689.jpeg 740 737 100713 97696 product 104236 M 0 104236
0 tom_whyld_winner_shop.jpg 794 794 101111 97582 product 104634 M 0 104634
0 Drop-the-Needle-EP.jpg 796 796 101245 97698 product 104768 M 0 104768
0 Drop-the-Needle-EP_68gu-o2.jpg 796 796 101246 97697 product 104769 M 0 104769