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 (
    6243, 6290, 4074, 6274, 6222, 6219, 3254, 
    4100, 4097, 6209, 6245, 6285, 6258, 
    6329, 6270, 6269, 6268, 6267, 6266, 
    6265
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00056

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 09.jpg 1400 1400 3267 3254 product 6455 M 0 6455
0 5052653288927.jpg 1000 1000 4085 4074 product 7608 M 0 7608
0 50526532279881344038431501c661f99afd.jpg 1000 1000 4108 4097 product 7631 M 0 7631
0 50526532279881344038442501c662a55e95.jpg 1000 1000 4111 4100 product 7634 M 0 7634
0 5052653233729.jpg 1000 1000 6218 6209 product 9741 M 0 9741
0 5052653260534.jpg 1000 1000 6228 6219 product 9751 M 0 9751
0 5052653277525.jpg 1000 1000 6231 6222 product 9754 M 0 9754
0 5052653299442.jpg 1000 1000 6252 6243 product 9775 M 0 9775
0 5052653243926.jpg 1000 1000 6254 6245 product 9777 M 0 9777
0 5052653218979.jpg 1000 1000 6267 6258 product 9790 M 0 9790
0 5052653227940134444069550228977a083c.jpg 1000 1000 6274 6265 product 9797 M 0 9797
0 505265322794013444406995022897b31a08.jpg 1000 1000 6275 6266 product 9798 M 0 9798
0 5052653227940134444070450228980b3660.jpg 1000 1000 6276 6267 product 9799 M 0 9799
0 50526532279401344440707502289833d7e8.jpg 1000 1000 6277 6268 product 9800 M 0 9800
0 50526532279401344440710502289866dbc9.jpg 1000 1000 6278 6269 product 9801 M 0 9801
0 5052653227940134444071350228989c92a2.jpg 1000 1000 6279 6270 product 9802 M 0 9802
0 5052653218986.jpg 1000 1000 6283 6274 product 9806 M 0 9806
0 5052653218962.jpg 1000 1000 6294 6285 product 9817 M 0 9817
0 5052653296977.jpg 1000 1000 6299 6290 product 9822 M 0 9822
0 5052653256490.jpg 1000 1000 6338 6329 product 9861 M 0 9861