Out of curiosity, try... SELECT transfer_date, regn_no FROM transfer_history_new WHERE regn_no = '039540' and transfer_date <= '2012-05-01' order by transfer_date asc
Is the problem that combining the order by with having transfer_date as the only returned item make it use the transfer_date index instead of the preferable regn_no index? > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Peter > Sent: Thursday, April 26, 2012 11:24 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Re Query planner creating a slow plan > > Marc L. Allen wrote, On 26/04/12 15:57: > > What indexes are on the underlying tables? > > > > There are indexes on all the fields used in the tables of the > transfer_history view. > > While tinkering I have discovered something: > > If instead of > SELECT transfer_date FROM transfer_history_new WHERE regn_no = '039540' > and transfer_date <= '2012-05-01' > order by transfer_date asc > > I write > > SELECT * FROM transfer_history_new > WHERE regn_no = '039540' and transfer_date <= '2012-05-01' > order by transfer_date asc > > then I get an execution time of a couple of milliseconds instead of > 300ms or so (times from Sqliteman this time). The planner has reverted > to using indexes instead of scans... > > -- > > 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
