Optimising a node join query

Question

I have a database of nodes and ways. A way contains two or more nodes. Some nodes belong to multiple ways, and are thus termed a "join" between two or more ways.

I'm trying to find all the nodes which join two or more ways. So I'm using this query,

SELECT * 
FROM way_nodes wl 
JOIN way_nodes wr 
ON wr.node_id = wl.node_id AND wr.way_id != wl.way_id

The way_nodes table contains the list of nodes in each way.

However it's hideously slow on my database with 9,021 ways and 43,706 nodes, and only gives me 20-30 nodes per second.

Initially I tried keeping counts of the number of times a node is used, but that still takes a long time.

I'm using SQLite3, but I suspect my problem applies to all databases. How would such a query be optimised?

Answer 1

A simpler way to find to find all the nodes which join two or more ways would be to count the distinct ways per node - like so:

SELECT node_id, count(distinct way_id)
FROM way_nodes
GROUP BY node_id
HAVING count(distinct way_id) > 1

Answer 2

Did you create indexes?