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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
