2

I have a table that I use to retrieve drive times, according to longitude and latitude of the origin and destination. Although I am using Google's API to find new commute times, I store the results of previous queries locally so that if I need to query the same drive again, I won't have to use Google. (Google's web service is throttled.) Anyway, the table includes five columns:

    LatStart (smallmoney)
    LongStart (smallmoney)
    LatEnd (smallmoney)
    LongEnd (smallmoney)
    DriveTime (Int.  In seconds)

(Smallmoney, it turns out, works very well for longitude and latitude, as long as you only need four digits after the decimal, which is precise enough for this.)

My question is: For optimal performance in SELECT queries, should I index all four longitude / Latitude columns?

And a second question: We can assume that it won't delete very often, but in the long run as the table grows, it will still insert rows about 25% as often as it selects rows. Therefore, will an index on all four columns be detrimental to insert queries?

PaulOTron2000
  • 975
  • 1
  • 12
  • 20
  • `smallmoney` type? See http://stackoverflow.com/questions/551894/whats-the-best-way-to-store-co-ordinates-longitude-latitude-from-google-maps answers for better representations. – Jesse C. Slicer Jul 12 '11 at 21:29
  • 2
    Jesse, thanks for that info. But my database doesn't support spatial data. I glanced over the whitepaper, and I'm not convinced there would be advantages in this case, because I only need four digits after the decimal, (which fits perfectly in the smallmoney data type) and the whole "curvature of the earth" issue they seem to be addressing is negligible, as my app is intended to be used within about a 50-mile radius. Ultimately, all answers regarding drive time come from Google's API, which depends on the roads, speed limits, etc. My question is really about indexing. – PaulOTron2000 Jul 12 '11 at 21:46

2 Answers2

1

If you're searching on all 4, then probably you'll want to index them.

Another factor that you should consider is the padding you put on your index. Higher padding reduces the amount of re-paging that has to be done on your index (it's not actually called re-paging, but the word is escaping me at the moment), which is a benefit for writing, and a cost for reads. Lower padding is better for reads.

I'd index all 4, and then play around with the padding a bit to find the best setting.

Chains
  • 11,681
  • 8
  • 40
  • 61
1

An index on the long/lat columns will help select (read) performance at the expense of insert/update/delete (write) performance.

Measure the performance yourself before and after adding the index to determine if it is appropriate.

JSR
  • 5,306
  • 2
  • 17
  • 23