On Sat, Feb 7, 2009 at 2:51 AM, Stefan de Konink <[email protected]> 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 _______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/dev
