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 (
    13878, 13877, 13876, 13875, 13874, 13873, 
    13872, 13870, 13869, 13829, 13862, 
    13858, 13850, 13849, 13848, 13847, 
    13846, 13845, 13844, 13843, 13842, 
    13841, 13840, 13839, 13838, 13837, 
    13836, 13835, 13834, 13833, 13832, 
    13831, 13830, 13827, 13826, 13825, 
    13824, 13823, 13822, 13821
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00078

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 13834 13821 product 17357 M 0
0 13835 13822 product 17358 M 0
0 13836 13823 product 17359 M 0
0 13837 13824 product 17360 M 0
0 13838 13825 product 17361 M 0
0 13839 13826 product 17362 M 0
0 13840 13827 product 17363 M 0
0 13842 13829 product 17365 M 0
0 13843 13830 product 17366 M 0
0 13844 13831 product 17367 M 0
0 13845 13832 product 17368 M 0
0 13846 13833 product 17369 M 0
0 13847 13834 product 17370 M 0
0 13848 13835 product 17371 M 0
0 13849 13836 product 17372 M 0
0 13850 13837 product 17373 M 0
0 13851 13838 product 17374 M 0
0 13852 13839 product 17375 M 0
0 13853 13840 product 17376 M 0
0 13854 13841 product 17377 M 0
0 13855 13842 product 17378 M 0
0 13856 13843 product 17379 M 0
0 13857 13844 product 17380 M 0
0 13858 13845 product 17381 M 0
0 13859 13846 product 17382 M 0
0 13860 13847 product 17383 M 0
0 13861 13848 product 17384 M 0
0 13862 13849 product 17385 M 0
0 13863 13850 product 17386 M 0
0 13871 13858 product 17394 M 0
0 13875 13862 product 17398 M 0
0 13882 13869 product 17405 M 0
0 13883 13870 product 17406 M 0
0 13885 13872 product 17408 M 0
0 13886 13873 product 17409 M 0
0 13887 13874 product 17410 M 0
0 13888 13875 product 17411 M 0
0 13889 13876 product 17412 M 0
0 13890 13877 product 17413 M 0
0 13891 13878 product 17414 M 0