[OSM-dev] Analysis of database point fields vs returned query records

[Nick Hill]

Analysis of database field size vs points returned performance.

I have prepared tables of query timings based on a 1M point field and a 
10M point field using MySQL4.0 debian stable on an AMD Sempron 32 bit 
2200 512M ram. These timings are for constraints on a table only 
containing the lat/lon.

These figures were achieved by first calculating a random point on the 
point field, then, assuming an even density distribution of points, an 
area was calculated to give the shown number of points. A query was then 
run for that calculated area. The time for 25 queries was summed then 
divided by 25, to give a mean average. The largest query size was 
initially run repeatedly until no appreciable reduction in query time 
was observed, to ensure caches were full before figures were taken. The 
Limit function was not used,


1 million point field

Records | Time
50	0.010s
100	0.013s
200	0.017s
400	0.05s
800	0.05s
1600	0.06
3200	0.077
6400	0.094
12800	0.12s
25600	0.15s	


10 Million point field

50	0.08s
100	0.1s
200	0.12s
400	0.16s
800	0.21s
1600	0.28s
3200	0.40s
6400	0.552
12800	0.80s
25600	1.20s