Re: [sqlite] .expert output help
On 26/4/62 23:56, Jose Isaias Cabrera wrote: this query. If you try ".expert -verbose", it will tell you the other This is the output from --verbose sqlite> .expert --verbose 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 '%diggs%' ...> ) AND a.InsertDate = ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID) ...> ...> GROUP BY a.ProjID; -- Candidates - (null) ... Maybe Candidates should say something else other than (null). That means it couldn't even come up with anything to try - you already have indexes for all WHERE constraints an ORDER/GROUP BY terms in the query. It should probably say "(no candidates found)", or something along those lines. Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .expert output help
Dan Kennedy, on Friday, April 26, 2019 12:13 PM wrote... >On 26/4/62 21:30, Jose Isaias Cabrera wrote: >Hey! Somebody tried it out! Thanks! :) I have been using it for a while. ;-) I have been adding INDEXes for queries used a lot... >this query. If you try ".expert -verbose", it will tell you the other This is the output from --verbose sqlite> .expert --verbose 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 '%diggs%' ...> ) AND a.InsertDate = ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID) ...> ...> GROUP BY a.ProjID; -- Candidates - (null) -- Query 1 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 '%diggs%' ) 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_Proje ct_ABT_Budget_1 (ProjID=?) CORRELATED SCALAR SUBQUERY 1 SEARCH TABLE Project_ABT_Budget USING COVERING INDEX PAB_ProjIDInsertDateProjIDI nsertDate (ProjID=?) Maybe Candidates should say something else other than (null). >schema (use the shell tool's ".schema" command). It makes it easier to Yes, I know this should be broken down better, but this is a reporting tool that I am creating to quickly and fast report factual project data by importing exports from a system. Instead of Excel scripts, SQLite takes the imports, keeps track of the changes, makes smart reporting to users and provide process suggestions to prevent projects from getting outside their original baseline. So, if here is the .schema, Dan. ;-) sqlite> .schema CREATE TABLE PMData ( login primary key, email, FirstName, LastName, Address, City, State, Zip, WorkPhone, Intelnet, HomePhone, Password, ComputerName, UserProfilePath , Beep, PMOHome, UseMailTo, SoundOff, UseLocalSharedDB, LastClarityUpdate, PMLocalDrive); CREATE TABLE Master_Project_List_Extra ( ProjID PRIMARY KEY, PMONotes, CurrYear, PlanYear, KeyProj, PMOStatus , BusApprDate, RepBoardDir, RepIberOpComm, Cost_Type, CompanyName, CurAgreedDel, InitPortYr, Department, FinancialStatus, FinancialType, Global_Local, WIP_Class, BusCaseVer, ITOpCo, InitAgreedEndDate, ChargeCode, ResOBSUnit, DepName, ITStrategy, Program, ResCode, Sponsor, InBusPlan, InPDS, InSubDept, LaunchRev, ALaborRes, CurAgrFinishDate, TimeIndiVal, TimeIndiColor, CostIndiVal, CostIndiColor, EstiDeliDate, PlannedProgess, ActualProgess, CommType, AnnualEACExt, BusCaseTotCost, AnnCostExt, EACTotCost, TimeMargin, ProgressDevVal, ProgressDevColor, CostDevVal, CostDevColor, EACExt, ActualsExt, EACvBusTotVal, EACvBusTotColor, EACvAnnPlanVal, EACvAnnPlanColor, EACvCBL, PlannedCostPcent, ActualCostPcent, TimevCBLKPIVal, TimevCBLKPIColor, AnnActualExt, AnnPlanCostPcent, ApprComDateTimeMargin, IncludedInPDS, IsActive, ProjCBLExt); CREATE TABLE `Bus_IT_Areas_ORGs` ( `IT_OBS`TEXT NOT NULL UNIQUE, `Area` TEXT, `Org` INTEGER, PRIMARY KEY(`IT_OBS`) ); CREATE TABLE Business_OBS_List ( Bus_OBS TEXT NOT NULL UNIQUE, Bus_Area, Bus_Org TEXT, PRIMARY KEY(Bus_Area) ); CREATE TABLE PMOTitles ( TitleKey PRIMARY KEY, Titles ); CREATE TABLE Project_List ( ProjID, CID, Project_Name, PMO_Board_Report, Project_Type, Start_date, Target_Go_Live_Date, Finish_Date, BL_Start, BL_Finish, Tot_CapexP, CapexP_Cur, Tot_OpexP, OpexP_Cur, Manager, Status_Indicator, SI_Color, Progress, status_comment, State, Business_Owner, BRD, Business_priority, BP_Color, First_BL, Updated_By, Updated_Dt, Status, Baseline_Cost, Baseline_Cur, Baseline_Time, Active, PMO_Percentage, PMO_Request, Financial_Status, Ann_CapexP, Ann_OpexP, YTD_Ann_Capex, YTD_Ann_Opex,
Re: [sqlite] .expert output help
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
Re: [sqlite] .expert output help
Simon Slavin on Friday, April 26, 2019 10:50 AM wrote... >On 26 Apr 2019, at 3:30pm, Jose Isaias Cabrera wrote: >Once you have defined those indices, execute the ANALZE command, then try >the .expert again. Thanks. >What led to you using WHERE 1=1 ? An apparent bug ? Long story. The app has many textboxes where the user can insert a string and hit ENTER and so a search is added to the query based on the textboxes that have data in them. So, having WHERE 1=1 makes it easy to have other search additions by just doing, AND (a.field_A LIKE %jic%) and adding another one, AND (b.field_B LIKE %something%) etc., etc. So, in the end the query ends up being something like this, 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 '%diggs%' AND (a.field_A LIKE %jic%) AND (a.field_B LIKE %something%) ) AND a.InsertDate = (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID) GROUP BY a.ProjID; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .expert output help
On 26 Apr 2019, at 3:30pm, Jose Isaias Cabrera wrote: > -- 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. Since there is no ideal index it needs to scan the list using something. That index seems to be fairly short and covers all the data needed, because all data can be retrieved directly from the index rather than needing an additional lookup in the table itself. > -- How can I create the INDEX on line 8 that sqlite_autoindex created? I think you mean SEARCH TABLE Project_ABT_Budget AS b USING COVERING INDEX sqlite_autoindex_Project_ABT_Budget_1 (ProjID=?) in which case the command is CREATE INDEX myName1 ON Project_ABT_Budget (ProjID) You should also do something like CREATE INDEX myName2 ON Project_List (InsertDate) CREATE INDEX myName3 ON Project_List (ProjID,InsertDate) Once you have defined those indices, execute the ANALZE command, then try the .expert again. What led to you using WHERE 1=1 ? An apparent bug ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .expert output help
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