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.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 = 'fr' 
WHERE 
  cscart_images_links.object_type = 'product' 
  AND cscart_images_links.type = 'A' 
  AND cscart_images_links.object_id IN (
    9881, 10438, 10441, 10440, 10444, 10427, 
    10419, 10459
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00084

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		11	Using index condition; Using filesort
1	SIMPLE	cscart_images	eq_ref	PRIMARY	PRIMARY	3	mahm3t_cs443.cscart_images_links.image_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
25201	9881	product	20788	A
29815	10419	product	25392	A
29816	10419	product	25393	A
29819	10419	product	25396	A
29820	10419	product	25397	A
29881	10427	product	25458	A
29882	10427	product	25459	A
29883	10427	product	25460	A
29971	10438	product	25548	A
29972	10438	product	25549	A
29973	10438	product	25550	A
29987	10440	product	25564	A
29988	10440	product	25565	A
29989	10440	product	25566	A
29990	10440	product	25567	A
29992	10441	product	25569	A
29993	10441	product	25570	A
29995	10441	product	25572	A
29996	10441	product	25573	A
29997	10441	product	25574	A
29998	10441	product	25575	A
29999	10441	product	25576	A
30035	10444	product	25612	A
30073	10444	product	25650	A
30074	10444	product	25651	A
30075	10444	product	25652	A
30076	10444	product	25653	A
30077	10444	product	25654	A
30192	10459	product	25769	A
30193	10459	product	25770	A
30199	10459	product	25776	A
30200	10459	product	25777	A
30201	10459	product	25778	A
30202	10459	product	25779	A
30235	9881	product	25812	A