[OSM-dev] [OSM-talk] donating read-only api-mirrors
Stefan de Konink
stefan at konink.de
Sat Feb 7 02:51:30 GMT 2009
Matt Amos wrote:
> On Fri, Feb 6, 2009 at 11:47 PM, Stefan de Konink <stefan at konink.de> wrote:
>> Matt Amos wrote:
>>> On Fri, Feb 6, 2009 at 8:32 PM, Stefan de Konink <stefan at konink.de> wrote:
>>>> Even then; the ordering can be done in SQL.
>>> not in the two-step scheme that Matthias was suggesting. it might be
>>> possible using temporary tables, but care would be necessary to work
>>> around mysql's horrible "can't re-open table" brokenness.
>> I really don't see how you could fetch *all* ways without the nodes that are
>> in the bbox or a join on the table.
> what are you talking about? i was merely making the simple observation
> that in the two-step node fetching scheme that Matthias was suggesting
> (fetch and stream nodes in bbox, fetch and stream nodes used by way)
> the fact that it is done in *two* queries with processing in-between
> means it is impossible to order the results using only SQL.
Ok do I understand you right in best readable case want to do the following?
SELECT * FROM nodes WHERE BBOX(...) OR id IN (
SELECT node FROM way_nds WHERE way IN (
SELECT way FROM way_nds WHERE node IN (
SELECT * FROM nodes WHERE BBOX(...)
) ORDER BY id;
But for performance reason Matthias would like to split this process in
two distinct parts, so reuse of the resultset is possible?
nodes := SELECT id FROM nodes WHERE BBOX(...)
ways := SELECT way FROM way_nds WHERE node IN (nodes);
SELECT * FROM nodes WHERE IN(nodes) OR IN(
SELECT node FROM way_nds WHERE way IN (ways);
And while busy...
SELECT * FROM ways WHERE id IN (ways);
> the node IDs in the bbox and extras used in ways would have to be
> merged and stored - which might be possible in a decent database - but
> mysql has a limitation where it doesn't allow temporary tables to be
> used twice in a statement, which makes things much more difficult.
As pointed out before, I am nowhere near a MySQL expert; but is MySQL
able to make soup from those partial results (vulcano wise it has a
caching advantage here), and might be smart enough to reuse the results.
I have benchmarked using my database engine that there is a tipping
point in reusing materialized resultsets (integers) versus rejoining
them. If reusing resultsets in MySQL is always cheap, no matter how big
the query that might be a good approach.
>>> adding an error element would require a (small) change to all clients
>>> to support it, which is fine if all the tool authors are willing to
>>> make the change (or accept a patch).
>> Yes, but since we are changing protocols soon, error messages would be a
>> minor implementation effort ;)
> indeed. but the API 0.6 changes were agreed a long time ago. adding
> even a "minor" change at this point would to be discussed and agreed
> with the tool authors.
I frankly don't want to make problems bigger than they are, is the only
error message currently/0.6 http headers? I am sure the parser will get
the message if we just send broken XML back ;)
More information about the dev