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

[Tom Hughes]

In message <47934202.4040707 at arjam.net>
          "Robert (Jamie) Munro" <rjmunro at arjam.net> wrote:

> Tom Hughes wrote:
> | 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 (....))
> 
> Can you not use 2 joins and a group_by?
> 
> select * from current_way
> ~           inner join current_nodes cn
> ~             on current_way.id = cn.id
> ~           inner join current_way_nodes cwn
> ~             on cn.id = cwn.id
> ~      where cn.tile in (....))
> ~      group by current_way.id
> 
> This shouldn't work (you should get errors about mixing group_by and
> normal fields) but AFAICR, with MySQL only, it does work. Otherwise you
> can do select max(foo) as foo, max(bar) as bar etc. for all the fields
> and get the same effect.

Well you can certainly use distinct and joins to get much the same
effect, and the optimiser does get the query right then, but you
introduce a temporary table and a sort which doesn't help performance
any.

Tom

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