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 (
    10167, 10152, 10168, 10148, 10145, 10170, 
    10147, 10143, 10146, 10153, 10144, 
    10150, 10151, 10149, 10154, 10156, 
    10169, 10171
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00130

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 25 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 image_id detailed_id type position emo_md5 image_path alt image_x image_y images_image_id
27270 10143 product 0 22845 A 0
27276 10145 product 0 22851 A 0
27285 10147 product 0 22860 A 0
27286 10147 product 0 22861 A 0
27291 10148 product 0 22866 A 0
27292 10148 product 0 22867 A 0
27294 10148 product 0 22869 A 0
27295 10148 product 0 22870 A 0
27296 10148 product 0 22871 A 0
27303 10149 product 0 22878 A 0
27304 10149 product 0 22879 A 0
27305 10149 product 0 22880 A 0
27306 10149 product 0 22881 A 0
27312 10150 product 0 22887 A 0
27313 10150 product 0 22888 A 0
27314 10150 product 0 22889 A 0
27330 10151 product 0 22905 A 0
27331 10151 product 0 22906 A 0
27336 10153 product 0 22911 A 0
27337 10153 product 0 22912 A 0
27338 10153 product 0 22913 A 0
27339 10153 product 0 22914 A 0
27340 10153 product 0 22915 A 0
27342 10154 product 0 22917 A 0
27343 10154 product 0 22918 A 0
27348 10156 product 0 22923 A 0
27349 10156 product 0 22924 A 0
27350 10156 product 0 22925 A 0
27360 10152 product 0 22935 A 0
27361 10145 product 0 22936 A 0
27362 10144 product 0 22937 A 0
27364 10151 product 0 22939 A 0
27447 10167 product 0 23022 A 0
27448 10167 product 0 23023 A 0
27454 10168 product 0 23029 A 0
27455 10168 product 0 23030 A 0
27456 10168 product 0 23031 A 0
27457 10168 product 0 23032 A 0
27458 10168 product 0 23033 A 0
27466 10169 product 0 23041 A 0
27467 10169 product 0 23042 A 0
27468 10169 product 0 23043 A 0
27478 10170 product 0 23053 A 0
27479 10170 product 0 23054 A 0
27486 10171 product 0 23061 A 0
27487 10171 product 0 23062 A 0
27488 10171 product 0 23063 A 0
30262 10146 product 0 25839 A 0
30263 10146 product 0 25840 A 0
27267 10143 product 0 22842 A 1
27271 10144 product 0 22846 A 1
27275 10145 product 0 22850 A 1
27334 10152 product 0 22909 A 1
27268 10143 product 0 22843 A 2
27273 10144 product 0 22848 A 2
27278 10145 product 0 22853 A 2
27333 10152 product 0 22908 A 2
27321 10143 product 0 22896 A 3
27322 10143 product 0 22897 A 4
27323 10143 product 0 22898 A 5
27324 10143 product 0 22899 A 6