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

Martijn van Oosterhout kleptog at gmail.com
Sun Jun 24 00:04:22 BST 2007


On 6/24/07, Tom Hughes <tom at compton.nu> wrote:
> Exactly. I'm not saying it isn't possible, just that I don't think
> it could be done efficiently enought to be useful.

Indeed, for the floating point case here it's not that useful, but for
the general case it is.

> Personally I've never seen a relational database execution plan
> from any relational, be it MySQL, Postgres, Oracle, DB2 or anything
> else that does what Martijn suggests.

# create temp table foo (a float, b float);
CREATE TABLE
# create index foo_index on foo(a,b);
CREATE INDEX
# set enable_seqscan=false;
SET
# explain select * from foo where (a > 100 and a < 200) and (b > 100
and b < 200);
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using foo_index on foo  (cost=0.00..4.91 rows=1 width=16)
   Index Cond: ((a > 100::double precision) AND (a < 200::double
precision) AND (b > 100::double precision) AND (b < 200::double
precision))
(2 rows)

The trick is that you oversimplified your description slightly. B-Tree
is a tree, and thus you can also move up and down. By moving up two
levels and then doing next you can skip over thousands of entries very
cheaply.

> What some will do is that if you have separate indexes on lat and
> lon they will read them both then look for common records from both
> to work out which records match. I doubt MySQL is that clever though.

Yeah, postgres will do that too..

> Unfortunately MySQL only does R-Tree indexes for MyISAM tables. At
> least one of the ones we're talking about is InnoDB.

That's the most annoying thing...

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




More information about the dev mailing list