[OSM-dev] Identification of "bottleneck" SQL code
nick at hogweed.org
nick at hogweed.org
Mon Apr 10 23:44:55 BST 2006
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
More information about the dev
mailing list