title: SQLite Query Performance Time
body: <p>I have this query which takes too much time (since last 1 hour is still running) to execute:</p>
  
  <pre><code>select RL.[LINK_ID] as LINK_ID, RPA.[POSTAL_AREA_ID] as POSTAL_AREA_ID, RRN.[STREET_NAME] as STREET_NAME 
  from RDF_LINK as RL, RDF_POSTAL_AREA as RPA, RDF_ROAD_LINK as RRL, RDF_ROAD_NAME as RRN 
  where RRL.[ROAD_NAME_ID] = RRN.[ROAD_NAME_ID]
  AND RPA.[POSTAL_AREA_ID] IN (RL.[LEFT_POSTAL_AREA_ID], RL.[RIGHT_POSTAL_AREA_ID]) 
  AND RL.[LINK_ID] = RRL.[LINK_ID] 
  </code></pre>
  
  <p>All the columns which are part of the query are indexed.<br>
  The ANALYZE command has already been. performed on database.<br>
  The database has approx. 73 millions records in the RDF_ROAD_LINK table and same number of records in other tables.</p>
  
  <p>Is there any other way around to write this query?</p>
  
  <pre><code>EXPLAIN QUERY PLAN
  select RL.[LINK_ID] as LINK_ID, RPA.[POSTAL_AREA_ID] as POSTAL_AREA_ID, RRN.[STREET_NAME] as STREET_NAME 
  from RDF_LINK as RL, RDF_POSTAL_AREA as RPA, RDF_ROAD_LINK as RRL, RDF_ROAD_NAME as RRN 
  where RRL.[ROAD_NAME_ID] = RRN.[ROAD_NAME_ID]
  AND RPA.[POSTAL_AREA_ID] IN (RL.[LEFT_POSTAL_AREA_ID], RL.[RIGHT_POSTAL_AREA_ID]) 
  AND RL.[LINK_ID] = RRL.[LINK_ID]
  </code></pre>
  
  <p>Output :: </p>
  
  <pre><code>0   0   3   SCAN TABLE RDF_ROAD_NAME AS RRN
  0   1   2   SEARCH TABLE RDF_ROAD_LINK AS RRL USING INDEX IND_ROAD_NAME_ID (ROAD_NAME_ID=?)
  0   2   0   SEARCH TABLE RDF_LINK AS RL USING INDEX sqlite_autoindex_RDF_LINK_1 (LINK_ID=?)
  0   3   1   SEARCH TABLE RDF_POSTAL_AREA AS RPA USING COVERING INDEX sqlite_autoindex_RDF_POSTAL_AREA_1 (POSTAL_AREA_ID=?)
  0   0   0   EXECUTE LIST SUBQUERY 1
  </code></pre>