I must be blind (something my wife would agree with)...but I did use "order by transfer_date"....the "asc" making no difference.
I showed you mine...you show me yours (on the same database please). sqlite> explain query plan SELECT transfer_date FROM transfer_history WHERE regn_no = '023674' and transfer_date<= '2012-05-01' order by transfer_date; 3|0|1|SCAN TABLE flock AS f (~161 rows) 3|1|0|SEARCH TABLE transfer AS tr USING INDEX tr_flock_no_index (flock_no=?) (~5 rows) 4|0|1|SCAN TABLE flock AS f (~161 rows) 4|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_org_flock_index (originating_flock=?) (~5 rows) 2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL) 5|0|1|SCAN TABLE flock AS f (~161 rows) 5|1|0|SEARCH TABLE sheep AS s USING INDEX sheep_reg_flock_index (registering_flock=?) (~2 rows) 1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL) 0|0|0|SCAN SUBQUERY 1 (~67 rows) 0|0|0|USE TEMP B-TREE FOR ORDER BY 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 3:57 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan Black, Michael (IS) wrote, On 26/04/12 21:39: > My fault...I thought I had extracted it under another name...turns out I was > using a different db...duh... > > Well we're none of us perfect. Only the female of the species can do more than two things at once... Yes, that query is fine. But add an 'order by transfer_date' clause and the planner no longer uses indexes but uses scans instead. Then if you do a select * ... it goes back to using indexes. With 100K+ records in the full database the difference in execution times is not insignificant. 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
