I suppose using a temporary table is out of the question? But, then again, that only solves the specific issue. I guess the more general question is how views with unions interact with aggregates and order by.
What happens if you don't use the view, but perform the query using the actual tables? > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Black, Michael (IS) > Sent: Thursday, April 26, 2012 10:11 AM > To: General Discussion of SQLite Database > Subject: [sqlite] Re Query planner creating a slow plan > > What happens if you use a subselect? > > > > selsect transfer_date from (select transfer_date from transfer_history > where regn_no='039540' and transfer_date <= '2012-05-01') order by > transfer_date; > > > > > > Michael D. Black > > Senior Scientist > > Advanced Analytics Directorate > > Advanced GEOINT Solutions Operating Unit > > Northrop Grumman Information Systems > > ________________________________ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] > on behalf of Peter [pe...@somborneshetlands.co.uk] > Sent: Thursday, April 26, 2012 7:00 AM > To: sqlite-users@sqlite.org > Subject: EXT :[sqlite] Query planner creating a slow plan > > Hi, > > I have a view 'transfer_history' which aggregates records from 3 tables > using UNION ALL. the aggregate is about 102k records. > > I have a query: > > SELECT transfer_date from transfer_history > WHERE regn_no = '039540' and transfer_date <= '2012-05-01' > > This returns three records and takes a couple of milliseconds - good. > > But if I add an 'order by' clause, or an aggregate (max) on > transfer_date, the time goes up to > 300ms. The reason seems to be that > the query planner uses scans for all three sub-queries instead of using > indexes on the underlying tables. > > With the basic query yhe QP says; > > SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1 > (regn_no=? AND transfer_date<?) (~1 rows) SEARCH TABLE sheep AS s USING > INDEX sqlite_autoindex_sheep_1 (regn_no=?) > (~1 rows) > COMPOUND SUBQUERIES 2 AND 3 (UNION ALL) > SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1 > (regn_no=?) > (~1 rows) > COMPOUND SUBQUERIES 1 AND 4 (UNION ALL) > > With the 'order by' clause the QP says: > > SCAN TABLE transfer AS tr (~49043 rows) > SCAN TABLE sheep AS s (~51858 rows) > COMPOUND SUBQUERIES 3 AND 4 (UNION ALL) > SCAN TABLE sheep AS s (~25929 rows) > COMPOUND SUBQUERIES 2 AND 5 (UNION ALL) > SCAN SUBQUERY 1 AS t2 (~4227 rows) > USE TEMP B-TREE FOR ORDER BY > > Seems to me it ought to be able to just sort the result of the first > plan. ATM it's an order of magnitude quicker at least to do the sort in > Python in the application. > > Pete > > -- > > Peter Hardman > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
