SQLite Forum

Views in LEFT JOIN queries not using index
Login

Views in LEFT JOIN queries not using index

(1.1) By Fernando Rodriguez (frodrig3) on 2021-03-26 06:34:50 edited from 1.0 [link] [source]

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

UPDATE 1

The above left join query plan uses SCAN TABLE when tested on sqlite 3.31 and 3.34. However, when I tested on 3.27, it appears to use SEARCH TABLE.

(2) By Gunter Hick (gunter_hick) on 2021-03-25 08:08:55 in reply to 1.0 [link] [source]

For a simple SELECT, doing UNION ALL on both component tables SEARCH result (0 to 1 records bcause of the UNIQUE constraint) is very efficient.

In a LEFT JOIN, all the records of the RHS are scanned, so materializing the view is expected to be faster.

(3.1) By Keith Medcalf (kmedcalf) on 2021-03-25 13:05:39 edited from 3.0 in reply to 2 [source]

In a LEFT JOIN, all the records of the RHS are scanned, so materializing the view is expected to be faster.

Why would all the records of the RHS of a left outer join be scanned? left outer join means do an inner join, and if there is no match, fill a "pretend" record with NULL and accept the resulting projection as a valid candidate ...

(4) By Gunter Hick (gunter_hick) on 2021-03-25 13:17:16 in reply to 3.1 [link] [source]

Let me reformulate: the RHS side of a JOIN is excpected to be visited "a lot", which would offset the cost of materializing it once vs searching both tables for each record on the LHS.

Running ANALYZE with a representative datatset loaded may change the computed costs and thus the query plan.

(5) By Keith Medcalf (kmedcalf) on 2021-03-25 13:26:04 in reply to 4 [link] [source]

There is no need. T3.Id is constrained UNIQUE so there can only be 0 or 1 record on the LHS of the LEFT JOIN, ever, no matter what data is in what table. This means that there can be either 0 or 1 lookups into the RHS unioned tables which may result in either no records (a NULL projection result), 1, or 2 result rows.

So there will always be either 0, 1, or 2 results. This fact is immutable.