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 compile your own? Or did you check to see that the indexes still weren't being used on your subset? I compiled the amalgamation: cc -o sqlite3 -O2 shell.c sqlite3.c -llpthread -ldl Running on Redhat 5. sqlite> SELECT transfer_date FROM transfer_history WHERE regn_no = '023674' and transfer_date<= '2012-05-01' order by transfer_date asc; 1995-04-04 00:00:00 1999-04-01 12:00:00 2002-03-31 12:00:00 sqlite> explain query plan SELECT transfer_date FROM transfer_history WHERE regn_no = '023674' and transfer_date<= '2012-05-01' order by transfer_date asc; 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:14 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 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: [home@system06 test]$ mkdir sqlite [home@system06 test]$ cd sqlite [home@system06 sqlite]$ wget http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip --2012-04-26 21:09:04-- http://www.somborneshetlands.co.uk/things/sss-test-nomem.zip Resolving www.somborneshetlands.co.uk<http://www.somborneshetlands.co.uk/>... 91.197.33.236 Connecting to www.somborneshetlands.co.uk|91.197.33.236|:80<http://www.somborneshetlands.co.uk%7c91.197.33.236%7c/>... connected. HTTP request sent, awaiting response... 200 OK Length: 184279 (180K) [application/zip] Saving to: `sss-test-nomem.zip' 100%[==============================================================================>] 184,279 792K/s in 0.2s 2012-04-26 21:09:04 (792 KB/s) - `sss-test-nomem.zip' saved [184279/184279] [home@system06 sqlite]$ unzip sss-test-nomem.zip Archive: sss-test-nomem.zip inflating: sss-test-nomem.sqlite [home@system06 sqlite]$ sqlite3 sss-test-nomem.sqlite SQLite version 3.7.11 2012-03-20 11:35:50 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select * from transfer_history where regn_no = '023674' ...> ; 023674|1610|Wycoller|1999-04-01 12:00:00|April 99|Presumed 023674|SSB900|(Dead)|2002-03-31 12:00:00|31/03/2002|Presumed 023674|1004|Glynwood|1995-04-04 00:00:00|4/4/95|Birth sqlite> Seems to work for me. You'll have to use 023674 instead of 039540 as the latter doesn't exist in this test database. 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
