Hi to all,
I have a table with many record, that have a field normally with value '0'.
Occasionally i update some records by setting the field to '1' to mark
it, and after in a background job, i localized them for working on it.
For that, i have an index on that field.
My problem: if i run an "analyze" when all records have the field equal
to '0', the localization (select .. where field != 0) don't use the
index, and do a full-table-scan.
If at least one record is set to '1', the index are used.
It's seem that "analyze" discover that all records have same field
value, so mark the index "useless".
Solutions that i know:
- run "analyze" after updating to '1' some records. :(
- maintain at least one fake record with '1' :(
- never launch "analyze" :(
Or... (please help!)
- exists some options to control this behiavour of analyze?
- it's possible to force the use of the index?
Thanks for feedback!
-- Test case:
-- Creating test table
CREATE TABLE os_test
(
code int NOT NULL,
mark int NOT NULL,
PRIMARY KEY (code)
);
-- Creating an index
CREATE INDEX os_test_index_mark ON os_test (mark);
-- Filling with some data
insert into os_test values ('a',0);
insert into os_test values ('b',0);
insert into os_test values ('c',0);
-- Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test WITH INDEX os_test_index_mark"
-- Right.
-- Launch "analyze"
analyze
-- Re-Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test"
-- Wrong.
-- Updating one record
update os_test set mark=1 where code='a'
-- Re-Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test"
-- Wrong.
-- Re-Analyze
analyze
-- Re-Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test WITH INDEX os_test_index_mark"
-- Right.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
