[sqlite] .expert output help

[Jose Isaias Cabrera]

Greetings.

I want to search the DB for all projects owned by "jic" and I have queried the 
DB with the following:
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> .expert
sqlite>  SELECT a.*,sum(b.AnnualDossier) as Dossier FROM Project_List AS a
   ...>  LEFT JOIN Project_ABT_Budget AS b ON a.ProjID = b.ProjID
   ...>  AND
   ...>  b.InsertDate =
   ...>  (SELECT MAX(InsertDate) FROM Project_ABT_Budget WHERE b.ProjID = 
ProjID)
   ...>  WHERE a.ProjID IN
   ...>  (
   ...>  SELECT a.ProjID FROM Project_List WHERE 1=1
   ...>  AND lower(a.Manager) LIKE '%jic%'
   ...>  ) AND a.InsertDate =
   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
   ...>
   ...>  GROUP BY a.ProjID;
(no new indexes)

SCAN TABLE Project_List AS a USING INDEX ProjID_InsertDate
CORRELATED LIST SUBQUERY 2
SCAN TABLE Project_List USING COVERING INDEX Project_Name
CORRELATED SCALAR SUBQUERY 3
SEARCH TABLE Project_List USING COVERING INDEX ProjID_InsertDate (ProjID=?)
SEARCH TABLE Project_ABT_Budget AS b USING COVERING INDEX 
sqlite_autoindex_Project_ABT_Budget_1 (ProjID=?)
CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE Project_ABT_Budget USING COVERING INDEX 
PAB_ProjIDInsertDateProjIDInsertDate (ProjID=?)

I have a few questions for the .experts :-),

-- On line 5 of the resulted output ,

SCAN TABLE Project_List USING COVERING INDEX Project_Name

Why is it scanning the table using that INDEX if there is no "Project_Name" 
referenced in the query? That is one of the fields of Project_List, but it is 
not being used now.

-- How can I create the INDEX on line 8 that sqlite_autoindex created?

Thanks for your knowledge sharing.

josé

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users