From: Peter Galbavy <peter.galbavy@knowtion.net>
To: pgsql-sql@postgresql.org
Subject: help optimise this ?
Date: Thu, 21 Nov 2002 14:22:51 -0000
Message-ID: <001d01c29169$79fd1d20$4528a8c0@cblan.mblox.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: peter.galbavy@knowtion.net
Subject: Re: help optimise this ?
In-Reply-To: <001d01c29169$79fd1d20$4528a8c0@cblan.mblox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox