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

Query time 0.00053

Result

product_id file_id file file_alt big_file_id big_file big_file_alt small_file_id small_file small_file_alt
26542 324071 https://s3.timeweb.cloud/482029f9-prod/products__images/26542_98bf6ef333f6_processed_lot_100194_20171129153255__G6A0887.JPG.jpg 324077 https://s3.timeweb.cloud/482029f9-prod/products__images/26542_1262560847b5_processed_lot_100194_20171129153255__G6A0887.JPG.webp 324079 https://s3.timeweb.cloud/482029f9-prod/products__images/26542_6d444105f5c5_processed_lot_100194_20171129153255__G6A0887.JPG.webp
26542 324082 https://s3.timeweb.cloud/482029f9-prod/products__images/26542_1e5fc9f83d70_processed_lot_100194_20171129153309__G6A0886.JPG.jpg 324089 https://s3.timeweb.cloud/482029f9-prod/products__images/26542_f916e4b97f69_processed_lot_100194_20171129153309__G6A0886.JPG.webp 324091 https://s3.timeweb.cloud/482029f9-prod/products__images/26542_4c0bb3e5e8f5_processed_lot_100194_20171129153309__G6A0886.JPG.webp
26542 324093 https://s3.timeweb.cloud/482029f9-prod/products__images/26542_6121a0cf1a41_processed_lot_100194_20171129153334__G6A0940.JPG.jpg 324099 https://s3.timeweb.cloud/482029f9-prod/products__images/26542_c8ff69ea0718_processed_lot_100194_20171129153334__G6A0940.JPG.webp 324103 https://s3.timeweb.cloud/482029f9-prod/products__images/26542_1edae0322490_processed_lot_100194_20171129153334__G6A0940.JPG.webp