help optimise this ?
От | Peter Galbavy |
---|---|
Тема | help optimise this ? |
Дата | |
Msg-id | 001d01c29169$79fd1d20$4528a8c0@cblan.mblox.com обсуждение исходный текст |
Ответы |
Re: help optimise this ?
|
Список | pgsql-sql |
I have a table of image 'instances' where the columns include: md5 char(32), -- the 'original' image md5 key file_md5 char(32) primary key, -- the md5 of each version of an image image_width int, image_length int I want to then find either the largest (max) or smallest (min) version of an image that falls within some range of sizes: e.g. select file_md5 from image_instance where image_width = (select min(image_width) from image_instance where md5 = '546b94e94851a56ee721f3b755f58462') and image_length = (select min(image_length) from image_instance where md5 = '546b94e94851a56ee721f3b755f58462') and md5 = '546b94e94851a56ee721f3b755f58462' and image_width between 0 and 160 and image_length between 0 and 160; Now, having to do three selects on 'md5' to limit the search seems a little unoptimal to me. Note that the test tables are small and I have no other indexes apart from the 'primary key' constraint yet - this is not my primary concern at this point, I would just like cleaner SQL. All I want back is (for some definition) the 'file_md5' that best matches my min/max criteria. I have not - and will leave for now - the case where a cropped image results in a scale change between width and length such that the min/max test returns a different set of rows for each dimension. Argh. And help given is greatly appreciated. rgds, -- Peter
В списке pgsql-sql по дате отправления: