[OSM-dev] OSM API Performance (Was: OSM Database Infrastructure)

[Tom Hughes]

In message <20080120105621.GP3665 at xosia.com> you wrote:

> > There are still issues with the way we query the database for the
> > map call, notably the need to fetch a list of nodes and then feed
> > a (potentially very large) list of node IDs back to the database
> > to find the matching ways.
> 
> What about doing the same thing as you do for nodes, making use of the
> quad tileing mechanism.
> 
> So you make a new table:
>  * quadtile (indexed)
>  * ways binary blob - list of 64bit ints of all ways that intersect this
> tile space
>  * relations binary blob - list of 64bit ints of all relations ...
> 
> Then you make the list of quad tile ids, and use that to get the nodes,
> and then use the same list to get the ways/relations.

I think that structure would be very expensive to update. It's not
really clear to me why you propose using a binary blob to be honest
thoough - why not just have columns for tile number and way id and
have one for each tile a way crosses?

We shouldn't really need to do all that though - we should be able
to achieve the same thing with a simple subquery like this:

  select * from current_way
  where id in (select cn.id from current_nodes cn
                            inner join current_way_nodes cwn
                            on cn.id = cwn.id
               where cn.tile in (....))

Which would find all the ways with nodes in one of the listed set
of tiles.

The only problem is that mysql can't optimise that query (or any
of a number of other ways of writing it that I've tried) properly
and rather than index scan current_nodes by tile and then work
back through the ids to the ways it does a table scan of the ways
looking up the nodes for each one to see if any of them match
the tile list.

Tom

-- 
Tom Hughes (tom at compton.nu)
http://www.compton.nu/