[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