sql - MySQL Joining three tables takes much time to execute

Question

my below select query that joins three tables (products, descriptions, images) takes 9 seconds to return just 10 rows!

SELECT `products`.id, `products`.serialNumber, `products`.title, `descriptions`.description1, `descriptions`.description2, `descriptions`.description3, `descriptions`.description4,`products`.price,`products`.colors, `products`.category, `products`.available, `products`.status, GROUP_CONCAT(DISTINCT `images`.file_name ORDER BY `images`.id) AS images 
FROM `products` 
INNER JOIN `descriptions`
ON `products`.id = `descriptions`.product_id 
LEFT JOIN `images` 
ON `products`.id = `images`.product_id
WHERE `products`.status = 1 
GROUP BY `products`.id
ORDER BY `products`.id DESC
LIMIT 10;

so after some research, I have arrived to the below answer which contains joining two tables and takes just one second to return the 10 rows, how I can add to it the third table (descriptions)? thank you

SELECT  *,
    ( SELECT  group_concat(`images`.file_name)
        FROM  `images`
    ) AS images
FROM  `products`
JOIN  `images` ON `products`.id = `images`.product_id
WHERE `products`.status = 1 
GROUP BY `products`.id
ORDER BY `products`.id DESC
LIMIT 10 

question from:https://stackoverflow.com/questions/65651477/mysql-joining-three-tables-takes-much-time-to-execute

Answer 1

for better performance be sure you have a composite index on table products columns

status, id 

on table descriptions and index on column

product_id 

on table image an index on column

 product_id 

Anyway you could join the 3 tables using a subquery for aggreagation and join this subquery

    SELECT  *, my_images.aggr_images
    FROM  `products`
    INNER JOIN `descriptions` ON `products`.id = `descriptions`.product_id 
    LEFT JOIN ( SELECT product_id, group_concat(`images`.file_name) aggr_images 
            FROM  `images`
            GROUP BY product_id
        ) AS my_images on my_images.product_id = `products`.id
    WHERE `products`.status = 1 
    ORDER BY `products`.id DESC
    LIMIT 10