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` = 28874

Query time 0.00059

Result

product_id file_id file file_alt big_file_id big_file big_file_alt small_file_id small_file small_file_alt
28874 414292 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_fe6fb7c6e0f3_33421_70400961a5c5_28874_5313c413a934_snimok-ekrana-2025-03-11-143040png 414293 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_7d8ee95e7500_33421_70400961a5c5_28874_5313c413a934_snimok-ekrana-2025-03-11-143040pngwebp 414294 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_03ff594b8741_33421_70400961a5c5_28874_5313c413a934_snimok-ekrana-2025-03-11-143040pngwebp
28874 414295 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_3a8123efeae5_33421_79dbaccdc64a_28874_4af82782a78b_snimok-ekrana-2025-03-11-143147png 414296 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_89d1fce24991_33421_79dbaccdc64a_28874_4af82782a78b_snimok-ekrana-2025-03-11-143147pngwebp 414297 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_e37ceca8a90d_33421_79dbaccdc64a_28874_4af82782a78b_snimok-ekrana-2025-03-11-143147pngwebp
28874 414298 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_4584d72df983_33421_0ccd32301bfd_28874_b73d51a97284_snimok-ekrana-2025-03-11-143217png 414299 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_154d99da3b72_33421_0ccd32301bfd_28874_b73d51a97284_snimok-ekrana-2025-03-11-143217pngwebp 414300 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_52e1d99df21d_33421_0ccd32301bfd_28874_b73d51a97284_snimok-ekrana-2025-03-11-143217pngwebp
28874 414301 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_39622cb7f7d0_33421_5fdf898bd591_28874_15c502577fb0_snimok-ekrana-2025-03-11-143245png 414302 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_f1855475b7ab_33421_5fdf898bd591_28874_15c502577fb0_snimok-ekrana-2025-03-11-143245pngwebp 414303 https://s3.timeweb.cloud/482029f9-prod/products__images/28874_f2d5304ce6a8_33421_5fdf898bd591_28874_15c502577fb0_snimok-ekrana-2025-03-11-143245pngwebp