[OSM-dev] [OSM-talk] donating read-only api-mirrors
Matt Amos
zerebubuth at gmail.com
Sat Feb 7 04:37:34 GMT 2009
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
More information about the dev
mailing list