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 (
    33254, 33242, 33256, 33188, 33166, 33156, 
    30927, 30717, 30722, 32940, 30583, 
    30529, 30453, 30452, 30403, 30402, 
    30300, 30294, 30291, 30255
  ) 
ORDER BY 
  cscart_images_links.position, 
  cscart_images_links.pair_id

Query time 0.00063

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 5054281053045.jpg 1000 1000 30272 30255 product 33795 M 0 33795
0 5054281024694.jpg 1000 1000 30308 30291 product 33831 M 0 33831
0 5054281047471.jpg 1000 1000 30311 30294 product 33834 M 0 33834
0 5054281011724.jpg 1000 1000 30317 30300 product 33840 M 0 33840
0 5054281079755.jpg 1000 1000 30420 30529 product 33943 M 0 33943
0 5054281058972.jpg 1000 1000 30475 30583 product 33998 M 0 33998
0 BS002.jpg 1000 1000 30657 30452 product 34180 M 0 34180
0 BS002_grbe-8c.jpg 1000 1000 30658 30453 product 34181 M 0 34181
0 AA049_1oyy-0d.jpg 1000 1000 30675 30402 product 34198 M 0 34198
0 AA049_t7s9-r2.jpg 1000 1000 30676 30403 product 34199 M 0 34199
0 5054281068506.jpg 1000 1000 30734 30717 product 34257 M 0 34257
0 BR029.jpg 3000 3000 30739 30722 product 34262 M 0 34262
0 5054281041684.jpg 1000 1000 30944 30927 product 34467 M 0 34467
0 5054281109148.jpg 1000 1000 32957 32940 product 36480 M 0 36480
0 5054281053496_l22o-wf.jpg 1000 1000 33173 33156 product 36696 M 0 36696
0 5054281077737.jpg 1000 1000 33183 33166 product 36706 M 0 36706
0 5054281106741.jpg 1000 1000 33205 33188 product 36728 M 0 36728
0 5054281096042.jpg 1000 1000 33259 33242 product 36782 M 0 36782
0 5054281096066.jpg 1000 1000 33271 33254 product 36794 M 0 36794
0 5054281106628.jpg 1000 1000 33273 33256 product 36796 M 0 36796