CODEWITHSUNDEEP
sqlsqlite
Hardik
Hardik

Reputation: 259

improve perfromance of query

I am using SQLite. I have a query which gets records after going through 6 different tables. Each table contain many records. The query below has been written based on the PK-FK relationship, but it's taking too much time to retrieve the data.

I can't be able to do Altering, Indexing on database.

Select distinct A.LINK_ID as LINK_ID,
                B.POI_ID
from RDF_LINK as A,
     RDF_POI as B,
     RDF_POI_ADDRESS as c,
     RDF_LOCATION as d,
     RDF_ROAD_LINK as e,
     RDF_NAV_LINK as f
where B.[CAT_ID] = '5800'
  AND B.[POI_ID] = c.[POI_ID]
  AND c.[LOCATION_ID] = d.[LOCATION_ID]
  AND d.[LINK_ID] = A.[LINK_ID]
  AND A.[LINK_ID] = e.[LINK_ID]
  AND A.[LINK_ID] = f.[LINK_ID]

Am I using the wrong method? Do I need to use IN?

EXPLAIN QUERY PLAN command output ::

0   0   3   SCAN TABLE RDF_LOCATION AS d (~101198 rows)
0   1   0   SEARCH TABLE RDF_LINK AS A USING COVERING INDEX sqlite_autoindex_RDF_LINK_1 (LINK_ID=?) (~1 rows)
0   2   5   SEARCH TABLE RDF_NAV_LINK AS f USING COVERING INDEX sqlite_autoindex_RDF_NAV_LINK_1 (LINK_ID=?) (~1 rows)
0   3   4   SEARCH TABLE RDF_ROAD_LINK AS e USING COVERING INDEX NX_RDFROADLINK_LINKID (LINK_ID=?) (~2 rows)
0   4   1   SEARCH TABLE RDF_POI AS B USING AUTOMATIC COVERING INDEX (CAT_ID=?) (~7 rows)
0   5   2   SEARCH TABLE RDF_POI_ADDRESS AS c USING COVERING INDEX sqlite_autoindex_RDF_POI_ADDRESS_1 (POI_ID=? AND LOCATION_ID=?) (~1 rows)
0   0   0   USE TEMP B-TREE FOR DISTINCT

Upvotes: 0

Views: 61

Answers (1)

CL.
CL.

Reputation: 180270

There is an AUTOMATIC index on RDF_POI.CAT_ID. This means that the database thinks it is worthwhile to create a temporary index just for this query. You should create this index permanently:

CREATE INDEX whatever ON RDF_POI(CAT_ID);

Furthermore, the CAT_ID lookup does not appear to have a high selectivity.

Run ANALYZE so that the database has a better idea of the shape of your data.

Upvotes: 1

Related Questions