Re: [sqlite] Help with INDEXing a query
Thanks. I didn't know this. From: Luuk Sent: Wednesday, April 3, 2019 02:34 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Help with INDEXing a query On 3-4-2019 19:34, Jose Isaias Cabrera wrote: > Never mind, guys. I was missing the INDEX for the table for the first left > join: > > CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID); > > Everything is nice, now. Thanks. > > > From: Jose Isaias Cabrera > Sent: Wednesday, April 3, 2019 01:02 PM > To: sqlite-users@mailinglists.sqlite.org > Subject: [sqlite] Help with INDEXing a query > > > Greetings! > > I am trying to speed up this query, > . sqlite> .expert sqlite> select * from testing where a=42; CREATE INDEX testing_idx_0061 ON testing(a); SEARCH TABLE testing USING INDEX testing_idx_0061 (a=?) sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with INDEXing a query
On 3-4-2019 19:34, Jose Isaias Cabrera wrote: Never mind, guys. I was missing the INDEX for the table for the first left join: CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID); Everything is nice, now. Thanks. From: Jose Isaias Cabrera Sent: Wednesday, April 3, 2019 01:02 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Help with INDEXing a query Greetings! I am trying to speed up this query, . sqlite> .expert sqlite> select * from testing where a=42; CREATE INDEX testing_idx_0061 ON testing(a); SEARCH TABLE testing USING INDEX testing_idx_0061 (a=?) sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with INDEXing a query
Never mind, guys. I was missing the INDEX for the table for the first left
join:
CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID);
Everything is nice, now. Thanks.
From: Jose Isaias Cabrera
Sent: Wednesday, April 3, 2019 01:02 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Help with INDEXing a query
Greetings!
I am trying to speed up this query,
SELECT a.*,b.*,c.Area,d.Bus_Area FROM Project_List AS a
LEFT JOIN Project_List_Extra AS b ON a.ProjID = b.ProjID
LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS
LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS
WHERE a.ProjID IN
(
SELECT a.ProjID FROM Project_List WHERE
a.Progress != 'Completed'
AND
a.PMO_Board_Report != 'No'
AND
(
(a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15') OR
(a.Target_Go_Live_Date = '' AND a.Finish_Date >
substr(date('now'),1,4) || '-01-15')
)
AND
a.InsertDate =
(SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
)
ORDER BY a.ProjID;";
By creating an INDEX for it. I have created these INDEXes:
CREATE INDEX PL_ProjID ON Project_List (ProjID);
CREATE INDEX ProjID_InsertDate ON Project_List (ProjID, InsertDate);
CREATE INDEX Manager ON Project_List (Manager);
CREATE INDEX ProjID_Progress_PMOBR_TGLD_FD_IDate ON Project_List (ProjID,
Progress, PMO_Board_Report, Target_Go_Live_Date, Finish_Date, InsertDate);
I thought this last one would fix it, but it still takes a good 14.88 seconds.
The above query results in 128 records, out of 128 * 10. Any help would be
greatly appreciated. Thanks.
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
[sqlite] Help with INDEXing a query
Greetings!
I am trying to speed up this query,
SELECT a.*,b.*,c.Area,d.Bus_Area FROM Project_List AS a
LEFT JOIN Project_List_Extra AS b ON a.ProjID = b.ProjID
LEFT JOIN Bus_IT_Areas_ORGs AS c ON a.IT_OBS = c.IT_OBS
LEFT JOIN Business_OBS_List AS d ON a.Business_OBS = d.Bus_OBS
WHERE a.ProjID IN
(
SELECT a.ProjID FROM Project_List WHERE
a.Progress != 'Completed'
AND
a.PMO_Board_Report != 'No'
AND
(
(a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15') OR
(a.Target_Go_Live_Date = '' AND a.Finish_Date >
substr(date('now'),1,4) || '-01-15')
)
AND
a.InsertDate =
(SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
)
ORDER BY a.ProjID;";
By creating an INDEX for it. I have created these INDEXes:
CREATE INDEX PL_ProjID ON Project_List (ProjID);
CREATE INDEX ProjID_InsertDate ON Project_List (ProjID, InsertDate);
CREATE INDEX Manager ON Project_List (Manager);
CREATE INDEX ProjID_Progress_PMOBR_TGLD_FD_IDate ON Project_List (ProjID,
Progress, PMO_Board_Report, Target_Go_Live_Date, Finish_Date, InsertDate);
I thought this last one would fix it, but it still takes a good 14.88 seconds.
The above query results in 128 records, out of 128 * 10. Any help would be
greatly appreciated. Thanks.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
