Hank <[EMAIL PROTECTED]> wrote on 04/19/2005 01:33:51 PM: > On 4/19/05, Keith Ivey <[EMAIL PROTECTED]> 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. > > -Hank > 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. from: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html ++++++++++++++++++++++++++ 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 ) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
