Sandbox

SELECT 
  cscart_images_links.*, 
  cscart_images.image_path, 
  cscart_common_descriptions.description AS alt, 
  cscart_images.image_x, 
  cscart_images.image_y, 
  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 (
    10067, 9851, 3320, 9842, 10104, 10063, 
    407, 8861, 405, 10201, 3319, 9849
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00124

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		12	Using index condition; Using filesort
1	SIMPLE	cscart_images	eq_ref	PRIMARY	PRIMARY	3	mahm3t_cs443.cscart_images_links.detailed_id	1	Using where
1	SIMPLE	cscart_common_descriptions	eq_ref	PRIMARY	PRIMARY	107	mahm3t_cs443.cscart_images.image_id,const,const	1	Using where

Result

pair_id	object_id	object_type	detailed_id	type	image_path	image_x	image_y	images_image_id
1066	407	product	1043	M	arz_solar_1.jpg	1000	1000	1043
1303	405	product	1869	M	arz-h1352580181509ebc556b624.jpg	600	508	1869
12441	8861	product	8344	M	118586-01-L.jpg	1280	960	8344
13539	9842	product	9126	M	velux_DFD.png	301	305	9126
13617	9849	product	9204	M	vlx_sss_000.jpg	1151	767	9204
26135	10067	product	21710	M	integra_klf_200_000.png	664	534	21710
26146	10104	product	21721	M	APF_02.jpg	1300	867	21721
27673	10201	product	23248	M	PTP-V-packshot-01_1_50.jpg	1000	1000	23248
28029	10063	product	23604	M	integra_kli_310_001_f4eh-48.png	220	220	23604
28573	3319	product	24148	M	atr_mua_001_j9k4-es.jpg	720	960	24148
28579	3320	product	24154	M	atr_mua_000_bo7v-mg.jpg	570	568	24154
30787	9851	product	26365	M	144841-01-s_icons_760x920.jpg	380	460	26365