Thanks for you answer MikeW. >Could you put the update records into their own separate table I already solved my problem using the newest "INDEXED BY". I'm trying to understand if is a SqLite limit or bug. And i'm trying to understand your answer about the hashes :(
For the moment, here you can found the comparison between SqLite, MySql and Oracle9 with the minimal full test-case. Only sqlite do a full-table-scan after an "analyze". But i'm not 100% sure about the right syntax for MySql and Oracle. ----------------------------------------------- SqLite (from new empty database) SQL> CREATE TABLE mytest ( mycode int NOT NULL, mymark int NOT NULL ); SQL> insert into mytest select 0,0; SQL> insert into mytest select * from mytest; -- rerun the lastest 21 times, until i reach 2 millions of records. SQL> create index myindex on mytest (mymark); SQL> explain query plan select mycode from mytest where mymark=1; detail: TABLE mytest WITH INDEX myindex SQL> analyze SQL> explain query plan select mycode from mytest where mymark=1; detail: TABLE mytest ----------------------------------------------- Oracle: SQL> CREATE TABLE mytest ( mycode int NOT NULL, mymark int NOT NULL ); SQL> insert into mytest select 0,0 from dual; SQL> insert into mytest select * from mytest; -- rerun the lastest 21 times, until i reach 2 millions of records. SQL> create index myindex on mytest (mymark) tablespace users; -- with sqlite-autotrace enabled SQL> select mycode from mytest where mymark=1; Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYTEST' 2 1 INDEX (RANGE SCAN) OF 'MYINDEX' (NON-UNIQUE) SQL> analyze table mytest compute statistics; SQL> explain query plan select mycode from mytest where mymark=1; Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=2) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MYTEST' (Cost=4 Card=1 Bytes=2) 2 1 INDEX (RANGE SCAN) OF 'MYINDEX' (NON-UNIQUE) (Cost=3 Card=1) ------------------------------------------- MySql: SQL> CREATE TABLE mytest ( mycode int NOT NULL, mymark int NOT NULL ); SQL> insert into mytest select 0,0; SQL> insert into mytest select * from mytest; -- rerun the lastest 21 times, until i reach 2 millions of records. SQL> create index myindex on mytest (mymark); SQL> explain select mycode from mytest where mymark=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE mytest ref myindex myindex 4 const 1 SQL> analyze table mytest; SQL> explain select mycode from mytest where mymark=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE mytest ref myindex myindex 4 const 1 > Don't have the source to hand, but it's possible that SQLite does indexes > by hash, in which case it wouldn't "know" that the field values are all > the same, just that they had the same hash-value, hence it will have > to do a linear search, which it will always have to do if hashes are the same. > > Could you put the update records into their own separate table, > then move them into the main one later - that would save all the slow > accesses. > > Regards, > MikeW > > > _______________________________________________ > 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
