CODEWITHSUNDEEP
sqliteindexingviewunion
Fernando Rodriguez
Fernando Rodriguez

Reputation: 33

Sqlite View does not use index for left join queries

In Sqlite, I defined a view as a union all of two tables. When I run queries using that view, the index is used if the query is simple enough. For certain complex queries, it does not and ends up running full table scans. Are there ways around this so that I can use views performantly?

Table/View Definitions:

CREATE TABLE 'Table1'     (Id varchar (18) PRIMARY KEY UNIQUE ON CONFLICT ROLLBACK,    Name varchar (255)    )
CREATE TABLE 'Table2'     (Id varchar (18) PRIMARY KEY UNIQUE ON CONFLICT ROLLBACK,    Name varchar (255)    )
CREATE TABLE 'Table3'     (Id varchar (18) PRIMARY KEY UNIQUE ON CONFLICT ROLLBACK,    Name varchar (255)    )
CREATE VIEW [UnionView] AS SELECT 'T1' tid, T1.rowid, T1.* FROM [Table1] T1 UNION ALL SELECT 'T2' tid, T2.rowid, T2.* FROM [Table2] T2

Simple Query (indexes are used):

SELECT Id FROM [UnionView] WHERE Id = 'asdf'

Explain Query Plan:

  1. COMPOUND QUERY
  2. LEFT-MOST SUBQUERY
  3. SEARCH TABLE Table1 AS T1 USING INDEX sqlite_autoindex_Table1_1 (Id=?)
  4. UNION ALL
  5. SEARCH TABLE Table2 AS T2 USING INDEX sqlite_autoindex_Table2_1 (Id=?)

LEFT JOIN Query (indexes are not used):

SELECT T3.Id FROM [Table3] T3 LEFT JOIN [UnionView] T ON T3.Id=T.Id  WHERE T3.Id = 'asdf'

Explain Query Plan

  1. MATERIALIZE 2
  2. COMPOUND QUERY
  3. LEFT-MOST SUBQUERY
  4. SCAN TABLE Table1 AS T1
  5. UNION ALL
  6. SCAN TABLE Table2 AS T2
  7. SEARCH TABLE Table3 AS T3 USING COVERING INDEX sqlite_autoindex_Table3_1 (Id=?)
  8. SCAN SUBQUERY 2 AS T

Upvotes: 1

Views: 516

Answers (1)

forpas
forpas

Reputation: 164099

Your complex query does full table scans of Table1 and Table2 because you are not doing any filtering on UnionView.
It does use though sqlite_autoindex_Table3_1.
Also, the WHERE clause is applied after the joins.

If you filter UnionView before the join then indexes will be used:

EXPLAIN QUERY PLAN 
SELECT T3.Id 
FROM [Table3] T3 
LEFT JOIN (SELECT Id FROM [UnionView] WHERE Id = 'asdf') T 
ON T3.Id=T.Id  
WHERE T3.Id = 'asdf'

Result:

  • MATERIALIZE 3
  • COMPOUND QUERY
  • LEFT-MOST SUBQUERY
  • SEARCH TABLE Table1 AS T1 USING INDEX sqlite_autoindex_Table1_1 (Id=?)
  • UNION ALL
  • SEARCH TABLE Table2 AS T2 USING INDEX sqlite_autoindex_Table2_1 (Id=?)
  • SEARCH TABLE Table3 AS T3 USING COVERING INDEX sqlite_autoindex_Table3_1 (Id=?)
  • SCAN SUBQUERY 3

Upvotes: 1

Related Questions