[OSM-dev] Identification of "bottleneck" SQL code

[nick at hogweed.org]

In case anyone is interested, I have identified some "bottleneck" SQL code. I 
also have a question about the current slippy map.

I have now added *all* UK OSM data into the database used by the test map 
drawing client at http://nick.dev.openstreetmap.org/index.php. This causes a 
huge slowdown, to unusably slow on the dev server. Upon testing the biggest 
bottleneck is the SQL which deals with ways.

Even after the ways code is removed, the SQL query to draw segments which 
cross a tile but have no nodes within (see dev list discussion, 30th March) 
causes a significant slowdown. This takes the form: (PHP):

 $result = mysql_query(
    "SELECT s.id,s.node_a,s.node_b,s.tags FROM segments as s, nodes as a, 
nodes as b where s.node_a=a.id and s.node_b=b.id and ( ((a.latitude between 
$south and  $north) or (b.latitude between $south and $north) or 
(a.latitude<$south and b.latitude>$north) or (b.latitude<$south and 
a.latitude>$north)) and ((a.longitude between $west and $east) or 
(b.longitude between $west and $east) or (a.longitude<$west and 
b.longitude>$east) or (b.longitude<$west and a.longitude>$east)))"
                         );

Maybe there is a faster way of doing this though?

Incidentally, how does the current OSM slippy map deal with segments which 
cross a tile but have no nodes within? It appears to do it, as there are no 
'broken' segments, but taking a glance at the code I can't see how. It calls 
the API to grab nodes and segments, but the API code has no special SQL to 
deal with this: all it does is grab nodes within the bounding box and 
segments connecting the nodes.

Nick