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 (
    96593, 96592, 96591, 96590, 96589, 97405, 
    96929, 96736, 96616, 94871, 97239, 
    97237, 96984, 96622, 97235, 97234, 
    97213, 97177, 97176, 97175
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00061

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 Nossarky_No_Fighting.jpg 3000 3000 97763 94871 product 101286 M 0 101286
0 5054285755679.jpg 1000 1000 99568 96589 product 103091 M 0 103091
0 5054285755679_mwf1-72.jpg 1000 1000 99569 96590 product 103092 M 0 103092
0 5054285755679_1eeu-3n.jpg 1000 1000 99570 96591 product 103093 M 0 103093
0 5054285755679_flc5-0i.jpg 1000 1000 99571 96592 product 103094 M 0 103094
0 5054285755679_y2m1-qd.jpg 1000 1000 99572 96593 product 103095 M 0 103095
0 RRR023.jpg 1500 1500 99597 96616 product 103120 M 0 103120
0 5054285949160.jpg 1000 1000 99603 96622 product 103126 M 0 103126
0 St16_fatControlelr-Tekneak_RMX.jpg 1181 1181 99717 96736 product 103240 M 0 103240
0 5054285975343.jpg 1000 1000 99912 96929 product 103435 M 0 103435
0 5054285976432.jpg 1000 1000 99967 96984 product 103490 M 0 103490
0 received_212496587299427.jpeg 1400 1400 100181 97175 product 103704 M 0 103704
0 Get_Messy_-_Dean_Richardson.jpg 1600 1600 100182 97176 product 103705 M 0 103705
0 Get_Messy_-_Tara_N.jpg 1600 1600 100183 97177 product 103706 M 0 103706
0 VRC004_Cover_FredrickScramble_TheUnseen.png 800 800 100220 97213 product 103743 M 0 103743
0 received_338422387881041.jpeg 1080 1080 100241 97234 product 103764 M 0 103764
0 received_168803688560231.jpeg 1080 1080 100242 97235 product 103765 M 0 103765
0 KT010.png 1400 1400 100244 97237 product 103767 M 0 103767
0 Our_House.png 2000 2000 100246 97239 product 103769 M 0 103769
0 Mangled_1000_x_1000.jpg 3000 3000 100472 97405 product 103995 M 0 103995