Make sure it gets the same results, but how about something like the below. The collate nocase on the cust field was also a good suggestion.
select XtraF as PortalID, ProjID, A_No as GTXNo, proj as ProjName, lang as Target, QuoteAvailable, DeliveryDate, sum(Xtra8) as PriceUSD, 0 as PriceCAD, sum(ProjFund) as TransferCost, XtraE as Department, XtraA as BillTo, pmuk as Contact, '-' as Notes1, '-' as Notes2 from LSOpenJobs as O inner join (select ProjID, max(edate) as QuoteAvailable from LSOpenJobs where PSubClass = 'QuoteAppr' group by ProjID) as I using (ProjID) inner join (select ProjID, min(edate) as DeliveryDate from LSOpenJobs where PSubClass = 'Delivery' group by ProjID) as E using (ProjID) where lower(cust) = 'xerox' and Xtra9 like '2017-09-%' group by ProjID, lang having sum(ProjFund) > 0; selectid|order|from|detail 1|0|0|SCAN TABLE LSOpenJobs USING INDEX OjPid 2|0|0|SCAN TABLE LSOpenJobs USING INDEX OjPid 0|0|1|SCAN SUBQUERY 1 AS I 0|1|0|SEARCH TABLE LSOpenJobs AS O USING INDEX OjPid (ProjID=?) 0|2|2|SEARCH SUBQUERY 2 AS E USING AUTOMATIC COVERING INDEX (ProjID=?) 0|0|0|USE TEMP B-TREE FOR GROUP BY Or maybe an index on PSubClass to speed up the subqueries? sqlite> create index idx1 on LSOpenJobs (PSubClass, ProjID); selectid|order|from|detail 1|0|0|SEARCH TABLE LSOpenJobs USING INDEX idx1 (PSubClass=?) 2|0|0|SEARCH TABLE LSOpenJobs USING INDEX idx1 (PSubClass=?) 0|0|1|SCAN SUBQUERY 1 AS I 0|1|0|SEARCH TABLE LSOpenJobs AS O USING INDEX OjPid (ProjID=?) 0|2|2|SEARCH SUBQUERY 2 AS E USING AUTOMATIC COVERING INDEX (ProjID=?) 0|0|0|USE TEMP B-TREE FOR GROUP BY -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of jose isaias cabrera Sent: Friday, October 20, 2017 2:22 PM To: SQLite mailing list Subject: [sqlite] Any change to make this query better? Greetings! This takes about 1.5 minutes to run with sqlite v3.20.1 with about 200K records sqlite> explain query plan ...> SELECT ...> O.XtraF AS PortalID, ...> O.ProjID, ...> O.A_No AS GTXNo, ...> O.proj AS ProjName, ...> O.lang AS Target, ...> (SELECT max(edate) from LSOpenJobs AS I WHERE I.ProjID = O.ProjID AND ...> I.PSubClass = 'QuoteAppr' ) AS QuoteAvailable, ...> (SELECT min(edate) from LSOpenJobs AS E WHERE E.ProjID = O.ProjID AND ...> PSubClass = 'Delivery') AS DeliveryDate, ...> sum(O.Xtra8) AS PriceUSD, ...> 0 AS PriceCAD, ...> sum(O.ProjFund) AS TransferCost, ...> O.XtraE AS Department, ...> O.XtraA AS BillTo, ...> O.pmuk AS Contact, ...> '-' AS Notes1, ...> '-' AS Notes2 ...> from LSOpenJobs AS O WHERE lower(cust) = 'xerox' AND ...> Xtra9 LIKE '2017-09-%' ...> GROUP BY ProjID,lang HAVING sum(ProjFund) > 0; 0|0|0|SCAN TABLE LSOpenJobs AS O USING INDEX OjPid 0|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE LSOpenJobs AS I USING INDEX OjPid (ProjID=?) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE LSOpenJobs AS E USING INDEX OjPid (ProjID=?) sqlite> _______________________________________________ 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
