[OSM-dev] Potlatch update

[Frederik Ramm]

Hi,

>> Pray that the SQL engine is smart enough to apply the bounding box
>> constraint first and not pair up all the nodes in the world ;-)
> 
> Unfortunately it isn't. :(
> 
> The query takes many, many minutes to complete. It runs at an almost  
> passable speed if you just use one half of the 'ON', i.e. ON  
> current_segments.node_a=current_nodes.id.

Someone once told me that Mysql is too dumb to use an index inside an OR 
condition and that one would therefore be better off with UNIONs. So, 
instead of

SELECT column1 FROM table WHERE column1="x" OR column2="x"

one would

SELECT column1 FROM table WHERE column1="x" UNION SELECT column1 FROM 
table WHERE column2="x"

I do not know if this may be the problem here (and what Mysql version 
the guy was talking about), and if it is, I have no idea how to 
translate the UNION solution into something you could use.

Oh wait.

SELECT current_nodes.id
    FROM current_nodes
    LEFT OUTER JOIN current_segments cs1
      ON cs1.node_a=current_nodes.id
    LEFT OUTER JOIN current_segments cs2
      ON cs2.node_b=current_nodes.id
WHERE (latitude BETWEEN 51.7047726979662 AND 51.8310050584275)
     AND (longitude BETWEEN -1.37151000827839 AND -1.11118408054402)
     AND cs1.id IS NULL
     AND cs2.id IS NULL
     AND current_nodes.visible=1

That might work?

Bye
Frederik

-- 
Frederik Ramm  ##  eMail frederik at remote.org  ##  N49°00.09' E008°23.33'