Eureka! was Re: [OSM-dev] SQL optimisation

[Nick Hill]

I have made further tests, and they show the approach I suggested using 
integers as keys does not show speed improvements with MyISAM mysql 
engine. I suspect the MySQL engine has an optimiser which considers the 
indexing scheme for fairly random data. An improvement indexing only on 
the integer field can produce a 25% improvement but this would not be 
compensated for by the time taken to deliver superfluous points.

I have made further tests with a 10 million point field using MyISAM and 
InnoDB with MySQL5.0.

It seems my default compiled version of MySQL5 is slightly slower than 
the debian version of MySQL4.0, and InnoDB (using hard disc file) is 
slower than MyISAM. I might also try InnoDB on a raw partition.