[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
query:
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?
TIA,
-joshua
More information about the thelist
mailing list