[thelist] SQL Index Optimization

Joshua Olson joshua at waetech.com
Tue Oct 22 12:48:01 CDT 2002

Hi Gang,

I am performing a lookup on some lat/long pairs.  I have the following

SELECT field1, field2, field3
  FROM areas
  WHERE Abs(latitude - 38.83204) < 0.434216239687
    AND Abs(longitude - 77.11996) < 0.557411089229
  ORDER BY field1

The above query tries to return all records within .4342 degrees latitude
and .5574 degrees longitude of 38.83204w 77.11996n.  The problem I have is
that the above query does not run very fast.  I thought it was and index
problem at first, then it occurred to me that by using the ABS I may be
telling the parser not to use an index at all.

So, then I added 2 indexes... one on latitude and one on longitude and
change that query into the following:

SELECT field1, field2, field3
  FROM areas
  WHERE latitude BETWEEN 38.3978237603 AND 39.2662562397
    AND longitude BETWEEN 76.5625489108 AND 77.6773710892
  ORDER BY field1

I only noticed a marginal speed improvement?

Here are my questions:

1.  What else could I do to speed up the query?
2.  What factors in the first query make it run slower than the second?



More information about the thelist mailing list