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 (
    100653, 100652, 100651, 100650, 100649, 
    101334, 101333, 101332, 101331, 101330, 
    101329, 101328, 101327, 101326, 101325, 
    101324, 101323, 101322, 101321, 101320
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00060

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.jpg 1000 1000 103731 100649 product 107254 M 0 107254
0 5054286304630_tc4m-7v.jpg 1000 1000 103732 100650 product 107255 M 0 107255
0 5054286304630_7x6q-6n.jpg 1000 1000 103733 100651 product 107256 M 0 107256
0 5054286304630_uvo2-2z.jpg 1000 1000 103734 100652 product 107257 M 0 107257
0 5054286304630_uaxa-qm.jpg 1000 1000 103735 100653 product 107258 M 0 107258
0 5054286368106_tvv3-iy.jpg 1000 1000 104403 101320 product 107926 M 0 107926
0 5054286368106_m7d8-bq.jpg 1000 1000 104404 101321 product 107927 M 0 107927
0 5054286368106_1avt-kc.jpg 1000 1000 104405 101322 product 107928 M 0 107928
0 5054286368106_l21j-vz.jpg 1000 1000 104406 101323 product 107929 M 0 107929
0 5054286368106_99hj-42.jpg 1000 1000 104407 101324 product 107930 M 0 107930
0 5054286368106_m7uv-6c.jpg 1000 1000 104408 101325 product 107931 M 0 107931
0 5054286368106_2aic-a1.jpg 1000 1000 104409 101326 product 107932 M 0 107932
0 5054286368106_fnb1-ne.jpg 1000 1000 104410 101327 product 107933 M 0 107933
0 5054286368106_v0mn-7n.jpg 1000 1000 104411 101328 product 107934 M 0 107934
0 5054286368106_fo11-wa.jpg 1000 1000 104412 101329 product 107935 M 0 107935
0 5054286368106_al45-f5.jpg 1000 1000 104413 101330 product 107936 M 0 107936
0 5054286368106_f1ix-rq.jpg 1000 1000 104414 101331 product 107937 M 0 107937
0 5054286368106_49gd-ak.jpg 1000 1000 104415 101332 product 107938 M 0 107938
0 5054286368106_bf2h-q6.jpg 1000 1000 104416 101333 product 107939 M 0 107939
0 5054286368106_qf03-r6.jpg 1000 1000 104417 101334 product 107940 M 0 107940