[OSM-dev] Proposed database improvement

[David Sheldon]

On Thu, Aug 31, 2006 at 10:26:53AM +0100, Nick Hill wrote:
> SELECT id, node_a, node_b, tags FROM current_segments where (node_a IN 
> (<nodelist>) OR node_b IN (<nodelist>))  and visible = true

> 2. A union. This will generate duplicate lines unsorted
> 3. Two queries into a temporary table then select distinctrow

How about a union and a distinctrow?


SELECT DISTINCTROW * FROM (
   SELECT id, node_a, node_b FROM current_segments where node_a IN 
	                   (<nodelist>) and visible = true
UNION
   SELECT id, node_a, node_b FROM current_segments where node_b IN 
	                   (<nodelist>) and visible = true
   )

Of course this may be implemented internally using a temporary table,
but we can leave that up to the query analyser.

Could you see how long that takes?

David
--