Hank <heskin@stripped> wrote on 04/19/2005 01:33:51 PM:
> On 4/19/05, Keith Ivey <keith@stripped> wrote:
> > Also, the index on zip_code, latitude, and longitude doesn't
> > make sense.
> Yeah - I didn't even notice the indexes in the table def (I used my
> own existing zip code table). That table def and query were obviously
> created by someone pretty green with SQL.
No, those indexes were intentional. If you read the section of the manual
on optimizing queries, you will encounter a page that mentions what are
known as "covering indexes". The advantage to a covering index is that if
your data is numeric and in the index, the engine can read the data
DIRECTLY from the index and completely skip all of the disk operations
required to read the information from the data file.
That index is a covering index and will make any query looking for just
lat and long against a zip code extremely fast because the engine will not
need to read the data file to get at the lat and long value because they
are already in the index.
In some cases, a query can be optimized to retrieve values without
consulting the data rows. If a query uses only columns from a table that
are numeric and that form a leftmost prefix for some key, the selected
values may be retrieved from the index tree for greater speed:
SELECT key_part3 FROM tbl_name WHERE key_part1=1
I guess the person who wrote that query wasn't so green after all, eh?
(BTW, I was not the author of the OP's query although there is a lot of
irony in this reply ;-D )
Unimin Corporation - Spruce Pine