CODEWITHSUNDEEP
sqlsqlite
Hardik
Hardik

Reputation: 259

SQLite Indexing

I am unable to decide about Indexing. Like I have below query which taking too much time for executing:

select count(rn.NODE_ID) as Count,
       rnl.[ISO_COUNTRY_CODE] as Country,
       rnl.[FUNCTIONAL_CLASS] as Functional_Class
from RDF_NODE as rn,
     RDF_LINK as rl,
     RDF_NAV_LINK as rnl 
where rl.[LINK_ID] = rnl.[LINK_ID]
  AND rn.NODE_ID IN (rl.[NONREF_NODE_ID], rl.[REF_NODE_ID])
GROUP BY rnl.[ISO_COUNTRY_CODE],
         rnl.[FUNCTIONAL_CLASS]

While I use EXPLAIN QUERY PLAN:

0 0 0 SCAN TABLE RDF_NODE AS rn USING COVERING INDEX NODE (~1000000 rows)
0 1 2 SCAN TABLE RDF_NAV_LINK AS rnl (~6645278 rows)
0 2 1 SEARCH TABLE RDF_LINK AS rl USING INDEX sqlite_autoindex_RDF_LINK_1 (LINK_ID=?) (~1 rows)
0 0 0 EXECUTE LIST SUBQUERY 1
0 0 0 USE TEMP B-TREE FOR GROUP BY

All the tables having Indexing.

What is the difference between SCAN and SEARCH? do we able to Change the Order?

Upvotes: 1

Views: 486

Answers (1)

CL.
CL.

Reputation: 180020

A SCAN goes through all rows in a table (in the order they are stored in the table, which is usually no order at all), while a SEARCH looks up single rows from a table.

SQLite implements all joins as nested loop joins. The outermost table is always accessed through a SCAN (except when there is a WHERE clause that restricts the rows to be returned). All the remaining tables should be accessed with a SEARCH to look up matching records; another SCAN indicates that there is no index that can used to speed up the lookup, so finding each match requires searching the entire table.

For this particular query, a large slowdown is the temporary table used to implement the GROUP BY. If all the grouping columns are in a single index, this is not necessary:

CREATE INDEX UseABetterIndexNameHere
    ON RDF_NAV_LINK(ISO_COUNTRY_CODE, FUNCTIONAL_CLASS);

(The join between RDF_NAV_LINK and RDF_LINK requires that RDF_LINK rows are looked up by their LINK_ID, so this column needs an index. Similarly, RDF_NODE needs an index on NODE_ID. But these indexes already exist.)

Upvotes: 1

Related Questions