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
