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 (
    100877, 100811, 100810, 100822, 100764, 
    100785, 100784, 100731, 100730, 100729, 
    100703, 100663, 100662, 100661, 100659, 
    100658, 100657, 100656, 100655, 100654
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00052

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 5054286304630_yqzz-jw.jpg 1000 1000 103736 100654 product 107259 M 0 107259
0 5054286304630_b6pm-8v.jpg 1000 1000 103737 100655 product 107260 M 0 107260
0 5054286304630_jzya-5r.jpg 1000 1000 103738 100656 product 107261 M 0 107261
0 5054286304630_e5ct-48.jpg 1000 1000 103739 100657 product 107262 M 0 107262
0 5054286304630_4pki-j0.jpg 1000 1000 103740 100658 product 107263 M 0 107263
0 5054286304630_4f2j-0u.jpg 1000 1000 103741 100659 product 107264 M 0 107264
0 5054286304630_gdn5-ok.jpg 1000 1000 103743 100661 product 107266 M 0 107266
0 5054286304630_l0l8-lk.jpg 1000 1000 103744 100662 product 107267 M 0 107267
0 5054286304630_x3c4-21.jpg 1000 1000 103745 100663 product 107268 M 0 107268
0 5054286315292.jpg 1000 1000 103787 100703 product 107310 M 0 107310
0 5054286321637.jpg 1000 1000 103813 100729 product 107336 M 0 107336
0 5054286321637_vcp8-yk.jpg 1000 1000 103814 100730 product 107337 M 0 107337
0 5054286298984.jpg 1400 1400 103815 100731 product 107338 M 0 107338
0 5054286327448.jpg 1000 1000 103847 100764 product 107370 M 0 107370
0 ODlogo_ll8z-26.jpg 700 700 103867 100784 product 107390 M 0 107390
0 ODlogo_lab0-tc.jpg 700 700 103868 100785 product 107391 M 0 107391
0 5054286337478.jpg 1000 1000 103893 100810 product 107416 M 0 107416
0 5054286337478_ijbx-hy.jpg 1000 1000 103894 100811 product 107417 M 0 107417
0 5054286335160.jpg 1000 1000 103905 100822 product 107428 M 0 107428
0 5054286340263.jpg 1000 1000 103960 100877 product 107483 M 0 107483