[OSM-dev] [OSM-talk] donating read-only api-mirrors

[Matt Amos]

On Sat, Feb 7, 2009 at 2:51 AM, Stefan de Konink <stefan at konink.de> wrote:
> 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;

well, i find join-style syntax easier, but essentially yes.

> 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);
> )

indeed, with this method either the node and way IDs are cached (i.e:
stored in memory) or the query is repeated (wasting time).

> And while busy...
>
> SELECT * FROM ways WHERE id IN (ways);

while simultaneously doing "select * from way_tags where id in (ways)"
and "select * from way_nodes where id in (ways)" and merging them for
output ;-)

> 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 wouldn't count on it. mysql's query optimiser is frequently wrong.

> 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.

there is probably a similar effect in mysql. but i am also not an
expert, so i don't know the necessary voodoo incantations to get the
best out of mysql ;-)

> 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 ;)

currently all errors are indicated by HTTP status codes. i'm sure
you'd agree that sending back broken content != indicating an error.
in HTTP-based protocols its a good idea to be standards compliant and
use the error reporting mechanisms that all clients (including wget,
curl, etc...) will understand.

cheers,

matt