On Fri, 20 Oct 2017 14:21:38 -0400 "jose isaias cabrera" <[email protected]> wrote:
> > 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> > You could try indexing by (ProjID, PSubClass, lower(cust)). You do all the work on the same table 'fake' joined with the result itself, LSOpenJobs. SELECT max(edate) from LSOpenJobs AS I WHERE I.ProjID = O.ProjID AND I.PSubClass = 'QuoteAppr' You can try a WITH with the main query and subselect from it to get those two values (max(edate) and min(edate)) HTH --- --- Eduardo Morras <[email protected]> _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
