title: How to improve sqlite SELECT performance?
body: <p>Some SELECT statements take several seconds to return data and I would like to know if and how I could improve performance. The DB normally is quite small (~10-40MB) but the larger it gets, the longer it takes.
  One example query which takes very long is the following:</p>
  
  <pre><code>SELECT intf_id FROM interfaces 
  WHERE intfType IN (SELECT intfType FROM interfaces 
  WHERE intf_id=39151) 
  AND macAddress IN (SELECT l2_addr FROM neighbor 
  INNER JOIN nlink ON nlink.neighbor_neighbor_id=neighbor.neighbor_id 
  INNER JOIN interfaces ON interfaces.intf_id=nlink.interfaces_intf_id 
  WHERE interfaces.intf_id=39151) 
  AND status LIKE 'UP' AND phl=1 AND intf_id &lt;&gt; 39151 
  </code></pre>
  
  <p>Maybe it's because of the nested SELECT statements?</p>
  
  <p>The DB Layout is as follows:
  <a href="https://i.stack.imgur.com/7CgiA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7CgiA.png" alt="DB Layout"></a></p>
  
  <p>EXPLAIN QUERY PLAN Output:
  <a href="https://i.stack.imgur.com/4kKkD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4kKkD.png" alt="enter image description here"></a></p>
  
  <p>EXPLAIN QUERY PLAN csv:</p>
  
  <pre><code>"0","0","0","SCAN TABLE interfaces USING COVERING INDEX ii1"
  "0","0","0","EXECUTE LIST SUBQUERY 1"
  "1","0","0","SEARCH TABLE interfaces USING INTEGER PRIMARY KEY (rowid=?)"
  "0","0","0","EXECUTE LIST SUBQUERY 2"
  "2","0","2","SEARCH TABLE interfaces USING INTEGER PRIMARY KEY (rowid=?)"
  "2","1","0","SCAN TABLE neighbor"
  "2","2","1","SEARCH TABLE nlink USING COVERING INDEX sqlite_autoindex_nlink_1 (neighbor_neighbor_id=? AND interfaces_intf_id=?)"
  </code></pre>