"D. Richard Hipp" <d...@hwaci.com> wrote in message news:4c2248d2-898d-41ec-81fc-bafdb726c...@hwaci.com... > > On Jan 11, 2010, at 9:28 PM, ve3meo wrote: >> Oddly enough, by revising the query to explicitly >> use an index, the two later releases are much faster... > > Did you run ANALYZE before you tried using explicit indices? > No. You have introduced me to it. So I tried using a SQLite manager with the 3.6.17 release. Shaved 25% off the unindexed (SQLite optimised only) query time to ~190s. Multiplied the previously fast explicitly indexed query from 3s to 210s. I was happy with the 3s.
Here are the EXPLAIN QUERY PLAN results before and after ANALYZE: Before: order,from,detail 0,0,TABLE EventTable, 1,1,TABLE FactTypeTable USING PRIMARY KEY, 2,2,TABLE FamilyTable USING PRIMARY KEY, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 0,0,TABLE EventTable, 1,1,TABLE FactTypeTable USING PRIMARY KEY, 2,2,TABLE FamilyTable USING PRIMARY KEY, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 0,2,TABLE NameTable WITH INDEX idxNamePrimary, 1,0,TABLE EventTable WITH INDEX idxOwnerDate, 2,1,TABLE FactTypeTable USING PRIMARY KEY, 0,0,TABLE NameTable WITH INDEX idxNamePrimary, 0,0,TABLE WitnessTable, 1,1,TABLE roletable USING PRIMARY KEY, 2,2,TABLE EventTable USING PRIMARY KEY, 3,5,TABLE FactTypeTable USING PRIMARY KEY, 4,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 5,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, After: order,from,detail 0,1,TABLE FactTypeTable, 1,2,TABLE FamilyTable, 2,0,TABLE EventTable WITH INDEX idxOwnerEvent, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 0,1,TABLE FactTypeTable, 1,2,TABLE FamilyTable, 2,0,TABLE EventTable WITH INDEX idxOwnerEvent, 3,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, 4,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 0,1,TABLE FactTypeTable, 1,2,TABLE NameTable WITH INDEX idxNamePrimary, 2,0,TABLE EventTable WITH INDEX idxOwnerEvent, 0,0,TABLE NameTable WITH INDEX idxNamePrimary, 0,1,TABLE roletable, 1,5,TABLE FactTypeTable, 2,2,TABLE EventTable, 3,4,TABLE NameTable AS NameTable2 WITH INDEX idxNameOwnerID, 4,0,TABLE WitnessTable WITH INDEX idxWitnessEventID, 5,3,TABLE NameTable AS NameTable1 WITH INDEX idxNameOwnerID, Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
