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... Looks like it uses the indexes just fine. I'm using the Window's EXE from the website. Also got the same result on Unix. Did you

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

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 19:00: Sqliteman must be pointing to the wrong database. sqlite> SELECT transfer_date FROM transfer_history WHERE regn_no = '039540'. Error: no such table: transfer_history Hmm. I've just done the following - cut & paste from my terminal:

Re: [sqlite] Re Query planner creating a slow plan

Re Query planner creating a slow plan Black, Michael (IS) wrote, On 26/04/12 18:21: > There is no transfer_table_new in that database. Or any view named > "transfer" anything. > > So what query are you running on this one? > > Sorry, that should be transfer_history. The n

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 18:21: There is no transfer_table_new in that database. Or any view named "transfer" anything. So what query are you running on this one? Sorry, that should be transfer_history. The name seems to have got corrupted during our email exchanges. I've

Re: [sqlite] Re Query planner creating a slow plan

on 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 11:54 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Re Query planner creating a slow plan Blac

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 17:05: What version are you using? Can you extract an example of all your tables/indexes/data to demonstrate? I'm on 3.7.11 (Arch-Linux). You can grab a database at http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip You will need to use a

Re: [sqlite] Re Query planner creating a slow plan

ion 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 10:46 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Re Query planner creat

Re: [sqlite] Re Query planner creating a slow plan

Simon Slavin wrote, On 26/04/12 16:39: On 26 Apr 2012, at 4:24pm, Peter wrote: > 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

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 16:38: And does this also work? Sounds like the planner isn't seeing all the columns in the view unless in the select. SELECT transfer_date,regn_no FROM transfer_history_new WHERE regn_no = '039540' and transfer_date<= '2012-05-01' order by

Re: [sqlite] Re Query planner creating a slow plan

Marc L. Allen wrote, On 26/04/12 16:38: 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

Re: [sqlite] Re Query planner creating a slow plan

On 26 Apr 2012, at 4:24pm, Peter wrote: > 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

Re: [sqlite] Re Query planner creating a slow plan

And does this also work? Sounds like the planner isn't seeing all the columns in the view unless in the select. SELECT transfer_date,regn_no FROM transfer_history_new WHERE regn_no = '039540' and transfer_date <= '2012-05-01' order by transfer_date asc Michael D. Black Senior Scientist

Re: [sqlite] Re Query planner creating a slow plan

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

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 =

Re: [sqlite] Re Query planner creating a slow plan

[sqlite] Re Query planner creating a slow plan > > Jim Morris wrote, On 26/04/12 15:36: > > It is possible using an alias would force better behavior: > > > > selsect theDate > > from (select transfer_date as theDate from transfer_history where > > regn_no='0395

Re: [sqlite] Re Query planner creating a slow plan

Jim Morris wrote, On 26/04/12 15:36: It is possible using an alias would force better behavior: selsect theDate from (select transfer_date as theDate from transfer_history where regn_no='039540' and transfer_date<= '2012-05-01') order by theDate Once again, it makes no difference - the

Re: [sqlite] Re Query planner creating a slow plan

Marc L. Allen wrote, On 26/04/12 15:30: I suppose using a temporary table is out of the question? It's much simpler (and probably quicker though I didn't test it) to read the three rows, sort them by date, pick the first row and use that. No need to generate random table names, create and

Re: [sqlite] Re Query planner creating a slow plan

It is possible using an alias would force better behavior: selsect theDate from (select transfer_date as theDate from transfer_history where regn_no='039540' and transfer_date<= '2012-05-01') order by theDate ___ sqlite-users mailing list

Re: [sqlite] Re Query planner creating a slow plan

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 cr

Re: [sqlite] Re Query planner creating a slow plan

Black, Michael (IS) wrote, On 26/04/12 15:11: select transfer_date from (select transfer_date from transfer_history where regn_no='039540' and transfer_date<= '2012-05-01') order by transfer_date; Makes no difference. Pete -- Peter Hardman ___