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 (
    74072, 74071, 74056, 73996, 73977, 73815, 
    73673, 73599, 73598, 73821, 73994, 
    73976, 73740, 73734, 73880, 73819, 
    73484, 73483, 73820, 73874, 73626, 
    73873, 73872, 73871, 73870, 73869, 
    73868, 73867, 73866, 73865, 73864, 
    73863, 73862, 73861, 73860, 73859, 
    73858, 73857, 73856, 73855
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00067

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 40 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 76295 73483 product 79818 M 0
0 76296 73484 product 79819 M 0
0 76410 73598 product 79933 M 0
0 76411 73599 product 79934 M 0
0 76439 73626 product 79962 M 0
0 76487 73673 product 80010 M 0
0 76548 73734 product 80071 M 0
0 76554 73740 product 80077 M 0
0 76630 73815 product 80153 M 0
0 76634 73819 product 80157 M 0
0 76635 73820 product 80158 M 0
0 76636 73821 product 80159 M 0
0 76669 73855 product 80192 M 0
0 76670 73856 product 80193 M 0
0 76671 73857 product 80194 M 0
0 76672 73858 product 80195 M 0
0 76673 73859 product 80196 M 0
0 76674 73860 product 80197 M 0
0 76675 73861 product 80198 M 0
0 76676 73862 product 80199 M 0
0 76677 73863 product 80200 M 0
0 76678 73864 product 80201 M 0
0 76679 73865 product 80202 M 0
0 76680 73866 product 80203 M 0
0 76681 73867 product 80204 M 0
0 76682 73868 product 80205 M 0
0 76683 73869 product 80206 M 0
0 76684 73870 product 80207 M 0
0 76685 73871 product 80208 M 0
0 76686 73872 product 80209 M 0
0 76687 73873 product 80210 M 0
0 76688 73874 product 80211 M 0
0 76728 73880 product 80251 M 0
0 76790 73976 product 80313 M 0
0 76791 73977 product 80314 M 0
0 76808 73994 product 80331 M 0
0 76810 73996 product 80333 M 0
0 76871 74056 product 80394 M 0
0 76886 74071 product 80409 M 0
0 76887 74072 product 80410 M 0