tags:

views:

36

answers:

2
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... :)

+1  A: 

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.

MartyIX
we don't know nothing about the application. maby there are only around 5 images or something?...but theres another argument for your case. using group_concat just consumes more database time and application server time is usually more scaleable and a better trade off ;)
Joe Hopfgartner
Thanks for comment! Well maybe there's 5 images BUT my experience is that every system complexity grows and therefore I would not write that in the first place.
MartyIX
A: 

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!

Joe Hopfgartner