title: Optimising a node join query
body: <p>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.</p>
  
  <p>I'm trying to find all the nodes which join two or more ways. So I'm using this query,</p>
  
  <pre><code>SELECT * 
  FROM way_nodes wl 
  JOIN way_nodes wr 
  ON wr.node_id = wl.node_id AND wr.way_id != wl.way_id
  </code></pre>
  
  <p>The way_nodes table contains the list of nodes in each way.</p>
  
  <p>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.</p>
  
  <p>Initially I tried keeping counts of the number of times a node is used, but that still takes a long time.</p>
  
  <p>I'm using SQLite3, but I suspect my problem applies to all databases. How would such a query be optimised? </p>