On 26/4/62 21:30, Jose Isaias Cabrera wrote:
Greetings.
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 :-),
Hey! Somebody tried it out! Thanks! :)
-- 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.
There's either an error or a strange construction created by a program
in this part of the query:
WHERE a.ProjID IN
...> (
...> SELECT a.ProjID FROM Project_List WHERE 1=1
...> AND lower(a.Manager) LIKE '%jic%'
...> )
The sub-query scans table "Project_List", but doesn't use any columns
from it (both a.ProjID and a.Manager are from the outer query). So, to
do the scan, SQLite is choosing the index it thinks will require the
minimum IO. i.e. one on very few fields.
-- How can I create the INDEX on line 8 that sqlite_autoindex created?
You have already done so. Index "sqlite_autoindex_Project_ABT_Budget_1"
is actually a PRIMARY KEY or UNIQUE constraint within the definition of
table "Project_ABT_Budget".
In this case, the key piece of output is "(no new indexes)". This means
that ".expert" thinks you have already created the optimal indexes for
this query. If you try ".expert -verbose", it will tell you the other
indexes it considered. Or, if you run it on a version of your db that
has no indexes at all, you can see that it will (hopefully!) recommend
indexes equivalent to those you already have.
For posts like these, unless you're constrained by company rules or some
other similar consideration, it's good to include the entire database
schema (use the shell tool's ".schema" command). It makes it easier to
answer questions regarding specific queries and allows people to
recreate your experiment themselves if they wish to investigate further.
Dan.
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
