[OSM-dev] Database Schema - MySql indexes

[Dan Moore]

> 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 
cache hits.

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...

cheers, dan.








       
____________________________________________________________________________________
Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
http://answers.yahoo.com/dir/?link=list&sid=396545469