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 = 'A' 
  AND cscart_images_links.object_id = 9823 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00097

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_images_links ref object_id object_id 81 const,const,const 3 Using index condition; Using where; 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 image_id detailed_id type position emo_md5 image_path alt image_x image_y images_image_id
31650 9823 product 0 27228 A 0 Okno-velux-GLL.jpg 2500 1875 27228
31651 9823 product 0 27229 A 0 Velux-GLL-dolne-otwieranie-scaled.jpg 1706 2560 27229
31652 9823 product 0 27230 A 0 velux-gllokno-1064b.jpg 1280 960 27230
31653 9823 product 0 27231 A 0 otwieranie-dolne-okno-dachowe-velux.jpg 460 351 27231
31654 9823 product 0 27232 A 0 drewniane-okno-dachowe-GLL-B.jpg 2500 1875 27232
31655 9823 product 0 27233 A 0 dachowe-okno-GLL-velux.jpg 2500 1875 27233
31656 9823 product 0 27234 A 0 nawiewnik-w-oknie-Velux-z-dolnym-otwieraniem-scaled.jpg 2560 1920 27234
31657 9823 product 0 27235 A 0 zdjecie-okno-1064b-velux-gll.jpg 1280 960 27235
31658 9823 product 0 27236 A 0 okno-velux-GLL-z-dolnym-otwieraniem.jpg 2500 1666 27236
31659 9823 product 0 27237 A 0 dodatki-okno-1064b-velux-gll.jpg 1280 960 27237