Eureka! was Re: [OSM-dev] SQL optimisation
nick at nickhill.co.uk
Sun Apr 9 15:31:07 BST 2006
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.
More information about the dev