[OSM-dev] Database Schema (was Re: [OSM-talk] Oh dear - planet has duplicate id's)

Martijn van Oosterhout kleptog at gmail.com
Sat Jun 23 17:46:10 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? Firstly, a logically btree index on lat+lon can
certainly be used for queries like:

lat < a and lat > b and lon < c and lon > d

and reading the mysql it docs tells me that a multi-column index would
be used in this case, since it uses a b-tree compatable operators and
references both columns. Have you actually examined the plan to check
that it really isn't using it? Because if it isn't it really needs to
be documented somewhere as a gotcha as it's rather unexpected.

Have a nice day,
Martijn van Oosterhout <kleptog at gmail.com> http://svana.org/kleptog/

More information about the dev mailing list