Mysql pick/join multiple rows in one row

Question

SELECT `a`.`department`, `a`.`category`, `a`.id, 
 (SELECT group_concat(t_0) FROM images WHERE ad_id=a.id) t_0,
 (SELECT group_concat(t_1) FROM images WHERE ad_id=a.id) t_1,
 (SELECT group_concat(t_1) FROM images WHERE ad_id=a.id) t_8 
 FROM `ads` AS `a`
 WHERE (a.department = 1) AND (a.category = 12) AND (a.charged=1) 
 ORDER BY `a`.`id` DESC

Is there better solution than this? I need to have all images from images table for each a.id(ad_id)

Thanks :)

edit...

aha it seems this is working:

SELECT `a`.`department`, `a`.`category`, `a`.id, group_concat(t_0), group_concat(t_1), group_concat(t_8)
 FROM `ads` AS `a`
 LEFT JOIN images i ON i.ad_id=a.id
 WHERE (a.department = 1) AND (a.category = 12) AND (a.charged=1) 
 GROUP BY a.id DESC

Don't know if this is right solution thou... :)

Answer 1

I don't think it's a good solution because you use group_concat function and the function has the limit known as group_concat_max_len. Therefore it may happen that you won't get the whole result. Even if the limit is high I would rather not use the group_concat because you may move your application to another server and the settings may be different.

I would simply divide the query in simpler queries and process them in PHP (if it is your case). Premature optimazation is not good.

Answer 2

your second approach looks not so bad but it depends on the size of tables and the number of successful joins... this will probably perform faster if you have a lot of imagse, but then you have only the images and have to link them to the other information in a second run:

select t_0,t_1,t_8,ad_id from images where ad_id is in (select id from ads  WHERE department = 1 AND category = 12 AND charged=1)

to sum it up if i understand your usecase correctly: its better to first see which ads need to be displayed, and then fetch the images for exactly this ad without any joins!