I know its better to recreate the index after the insert, but it seems like the index is not being updated or is for some reason no longer useful after the insert.
Before the inserts i have an index on visitors(suburb); This is my insert statement i insert 10000 values: INSERT INTO visitors VALUES ( :id, strftime('%s','now', 'localtime'), :name, :suburb, :town, strftime('%s','now', 'localtime')); sqlite3_prepare_v2 (db, sql, strlen (sql) + 1, &stmt, NULL); i am also deleting the values after the insert sqlite3_exec(db, "DELETE FROM visitors where id >200000;", NULL, NULL, 0); after queries above i run this select suburb, count(*) from visitors WHERE entry_time >= (strftime('%s' ,'now', 'start of day', 'localtime')) group by suburb; CPU Time: user 3.057620 sys 0.358802 0|0|0|SCAN TABLE visitors (~19998 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY time after i recreate the index CPU Time: user 1.934412 sys 0.343202 0|0|0|SCAN TABLE visitors USING INDEX idx (~19998 rows) -- View this message in context: http://old.nabble.com/INDEX-not-working-after-insert%27s-tp30652638p30652638.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users