SELECT 
  PI.product_id, 
  F1.id AS file_id, 
  F1.path AS file, 
  (
    SELECT 
      PI1.alt 
    FROM 
      `cscart_products__images` PI1 
    WHERE 
      PI1.original_file_id = F1.id
  ) AS file_alt, 
  F2.id AS big_file_id, 
  F2.path AS big_file, 
  (
    SELECT 
      PI2.alt 
    FROM 
      `cscart_products__images` PI2 
    WHERE 
      PI2.watermark_big_file_id = F2.id
  ) AS big_file_alt, 
  F3.id AS small_file_id, 
  F3.path AS small_file, 
  (
    SELECT 
      PI3.alt 
    FROM 
      `cscart_products__images` PI3 
    WHERE 
      PI3.watermark_small_file_id = F3.id
  ) AS small_file_alt 
FROM 
  `cscart_products__images` AS `PI` 
  LEFT JOIN `files` AS `F1` ON `F1`.`id` = `PI`.`original_file_id` 
  LEFT JOIN `files` AS `F2` ON `F2`.`id` = `PI`.`watermark_big_file_id` 
  LEFT JOIN `files` AS `F3` ON `F3`.`id` = `PI`.`watermark_small_file_id` 
WHERE 
  `PI`.`product_id` = 27206

Query time 0.00064

Result

product_id file_id file file_alt big_file_id big_file big_file_alt small_file_id small_file small_file_alt
27206 204235 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_2b9d23227603_processed_lot_53481_20141117144053.jpg 204248 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_171737df2404_processed_lot_53481_20141117144053.webp 204256 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_857820321296_processed_lot_53481_20141117144053.webp
27206 204262 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_c7f055907fbb_processed_lot_53481_20141117144110.jpg 204274 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_751ddada8b5c_processed_lot_53481_20141117144110.webp 204282 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_c1b3b99a9fc5_processed_lot_53481_20141117144110.webp
27206 204286 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_11a389e7cbc8_processed_lot_53481_20141117144127.jpg 204300 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_3dd8b0ff8006_processed_lot_53481_20141117144127.webp 204305 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_87c7867886d8_processed_lot_53481_20141117144127.webp
27206 204314 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_ac639935a47c_processed_lot_53481_20141117144143.jpg 204326 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_4a4f12a5b63e_processed_lot_53481_20141117144143.webp 204330 https://s3.timeweb.cloud/482029f9-prod/products__images/27206_888ade7a7846_processed_lot_53481_20141117144143.webp