Why is this very fast (20 ms): --------
SELECT table1.data1, table1.data2 FROM table1 WHERE table1.data1 = 10 ORDER BY table1.rowid DESC LIMIT 250 OFFSET 500000 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows) -------- And this very slow (3500ms): -------- SELECT table1.data1, table2.data2 FROM table1 JOIN table2 ON table1.rowid=table2.rowid WHERE table1.data1 = 10 ORDER BY table1.rowid DESC LIMIT 250 OFFSET 500000 0 0 0 SEARCH TABLE table1 USING INDEX data1idx (data1=?) (~3 rows) 0 1 1 SCAN TABLE table2 VIRTUAL TABLE INDEX 1: (~0 rows) -------- The values in table1.data2 and table2.data2 are 100% identical, because my app has always duplicated all columns of table2 (fts4 table) into table1 to work around this issue. But I really like to solve it some day, because it doubles my database size. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
