Adrian Lynch wrote: > Jochem, how about sub selects in the main select list? Would this be more > intensive than the aggregate function way? From the looks of it, I'd guess > it would do two extra queries per row of tblModelStuff. Is this better or > worse than your way?
That depends on the table statistics (number of rows etc.), indexes and on how smart the database is. Try it :-) > With this you do get more control over what image you > return, if the ID denotes which image comes first MAX()/MIN() will work in > the sub select, or there may be some other flag in the image table to use. A flag in the image table to identify the 'preferred' image together with the right indexes will in most databases be the fastest solution. Ideally you would use a partial index on ImageID & ImageName WHERE flag = TRUE so you would get an index-only lookup over the smallest possible index. (Not that I know any database that can combine all these techniques :) However, before going overboard on optimizing a small problem I would focus on the LIKE predicate. Very few databases will be able to avoid a full table scan on a LIKE and that is probably the most expensive operation. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware: a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:191457 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
