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 (
    84954, 84953, 84952, 84950, 84948, 84906, 
    84905, 84904, 84903, 84902, 84901, 
    84900, 84899, 84898, 84897, 84896, 
    84895, 84628, 84627, 84600, 84599, 
    84597, 84943, 84942, 84919, 84918, 
    84917, 84916, 84915, 84639, 84549, 
    84977, 84811, 84810, 84809, 84808, 
    84807, 84806, 84805, 84804
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00082

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 87473 84549 product 90996 M 0
0 87521 84597 product 91044 M 0
0 87523 84599 product 91046 M 0
0 87524 84600 product 91047 M 0
0 87551 84627 product 91074 M 0
0 87552 84628 product 91075 M 0
0 87563 84639 product 91086 M 0
0 87730 84804 product 91253 M 0
0 87731 84805 product 91254 M 0
0 87732 84806 product 91255 M 0
0 87733 84807 product 91256 M 0
0 87734 84808 product 91257 M 0
0 87735 84809 product 91258 M 0
0 87736 84810 product 91259 M 0
0 87737 84811 product 91260 M 0
0 87835 84895 product 91358 M 0
0 87836 84896 product 91359 M 0
0 87837 84897 product 91360 M 0
0 87838 84898 product 91361 M 0
0 87839 84899 product 91362 M 0
0 87840 84900 product 91363 M 0
0 87841 84901 product 91364 M 0
0 87842 84902 product 91365 M 0
0 87843 84903 product 91366 M 0
0 87844 84904 product 91367 M 0
0 87845 84905 product 91368 M 0
0 87846 84906 product 91369 M 0
0 87855 84915 product 91378 M 0
0 87856 84916 product 91379 M 0
0 87857 84917 product 91380 M 0
0 87858 84918 product 91381 M 0
0 87859 84919 product 91382 M 0
0 87884 84942 product 91407 M 0
0 87885 84943 product 91408 M 0
0 87890 84948 product 91413 M 0
0 87892 84950 product 91415 M 0
0 87894 84952 product 91417 M 0
0 87895 84953 product 91418 M 0
0 87896 84954 product 91419 M 0
0 87919 84977 product 91442 M 0