[OSM-dev] Database Schema - MySql indexes
writetodan at yahoo.com
Tue Jun 26 23:00:40 BST 2007
> On 6/23/07, Tom Hughes <tom at compton.nu> wrote:
> > - Split the current index on lat+lon into two separate indexes
> > for each axis - as it stands the lon component of the index can
> > only be used when exactly one latitude has been selected (more
> > or less impossible as it is floating point) so is never used. With
> > separate indexes the database will be able to decide which is most
> > efficient for each query and the keys will be smaller so there
> > will be less I/O for whichever index it uses.
> Are you sure?
[snip well-argued declaration of surety]
the reason that a composite index on lat/long is more efficient than
single/separate indexes for range/window queries is that MySql only
needs load the indexes and does a key scan, not a table scan - it's
a question of disk access and memory usage (cache fit)...
Jon Burgess wrote:
> PostgreSQL does do this efficiently but only by using R-Tree indexes.
> MySQL supports these for spatial types too since 4.1. The MySQL spatial
> support is fairly limited but should be sufficient for the simple
> bounding box queries used by the map API.
again, although it seems a no-brainer to use MySQL db-level spatial
indexes, unfortunately they seem to suffer from memory usage and
fragmentation problems - this is exacerbated by the multi-user
environment where queries are more geographically diverse within
a short timeframe - i.e. requires much larger cache or get less
i'm not really sure how good postgresql SI is in these
circumstances, although the indicated (64-byte?) node sizes say it
might not be much/any better.
so, as theoretically (computationally) poor as the b-tree (compound)
indexes are, in the practical world, under our concurrency
constraints, their much smaller size punctures many assumptions...
> yet another good reason to switch ?
umm, not convinced...
Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
More information about the dev