[OSM-dev] SQL optimisation

[Nick Hill]

The database appears sane given the latitude appears before longitude on 
the GPX database, and the queries select in the same order.

However, I think we could optimise substantially. I haven't tried this 
optimisation, I am currently having trouble gettign the user set-up 
working on a system I deployed here. In any case, I will float the idea:

Referring to the gps_points table and the points_idx index. The index 
has columns latitude, longitude, user_id.

For each selected latitude range, almost every entry in the selected 
latitude data set will be different. There will therefore not be any use 
using the index to select a subset of longitudes for every selected 
latitude value.  I expect the longitude and user_id indexes are 
completely ignored, and may as well be deleted.

How to optimise:
Sample the data such that for every selected area, the first selected 
key will have many second selectable keys.

For example;
In the gps_points table, delete superfluous indexes on latitude and 
longitude. These include:
longitude and user_id on the points_idx
Add two columns longitude_int and latitude_int with type smallint.
Add a new key called int_key with columns in this order:
latitude_int Longitude_int latitude

Populate each column with latitude_int=int(longitude*100) and 
longitude_int=int(longitude*100)

When querying the database, use a query like:
select distinctrow latitude, longitude from gps_points where 
latitude_int>(int(#{lat1}*100) and latitude_int<(int(#{lat2}*100) and 
longitude_int>(int(#{lon1}*100) and longitude_int<(int(#{lon2}*100) and 
latitude > #{lat1} and latitude < #{lat2} and longitude > #{lon1} and 
longitude < #{lon2}

This is almost certainly not valid ruby, but should give the idea.

This should enable effective indexing on both lat and lon, improving 
data asccess speed on all tables which use a very wide range of input 
values on two indexes.

I intend to try this once I have my dev set-up working.