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 (
    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.00096

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-321-Artwork.jpg 3000 3000 95786 92922 product 99309 M 0 99309
0 Vicious-Circle-Recordings-322-Artwork.jpg 3000 3000 95787 92923 product 99310 M 0 99310
0 Frantic_sleeve_FD051-CLK-_-Lee-Coulson---Untethered_1MB.jpg 3000 3000 97663 94775 product 101186 M 0 101186
0 TBHH034_Tom_Basquil___Baseglitz_-_Instabitch_Artwork-min.jpg 3000 3000 99159 96183 product 102682 M 0 102682
0 Spectrum_g2va-25.jpg 1100 1100 99534 96557 product 103057 M 0 103057
0 St14_WorkItOut.jpg 1181 1181 99715 96734 product 103238 M 0 103238
0 St15_DeadIsBetter.jpg 1181 1181 99716 96735 product 103239 M 0 103239
0 TMP0179.png 2160 2160 99872 96890 product 103395 M 0 103395
0 ESP_Turcois_p9i4-vq.png 400 400 99915 96932 product 103438 M 0 103438
0 received_493437708648026.jpeg 2048 2048 99916 96933 product 103439 M 0 103439
0 Acid_Inexperience.jpg 1750 1750 99969 96986 product 103492 M 0 103492
0 FR134_-_Random_But_Raw_-_Big_Bullet.jpg 2000 2000 99974 96991 product 103497 M 0 103497
0 FR135_-_Random_But_Raw_-_Knocker.jpg 2000 2000 99975 96992 product 103498 M 0 103498
0 TRI006_-_TriBeaut_-_Night_Night.jpg 3000 3000 99976 96993 product 103499 M 0 103499
0 044.jpg 1000 1000 100006 97022 product 103529 M 0 103529
0 045.jpg 1000 1000 100007 97023 product 103530 M 0 103530
0 WhatsApp_Image_2021-06-19_at_16.08.44.jpeg 1080 1080 100016 97030 product 103539 M 0 103539
0 Beatbox.png 2000 2000 100022 97037 product 103545 M 0 103545
0 JAM039_David_Morrish_-_Stay.jpg 1400 1400 100178 97172 product 103701 M 0 103701
0 Guttural_Remix.jpg 1750 1750 100179 97173 product 103702 M 0 103702