Fabrizio Carimati <[EMAIL PROTECTED]> writes: > > 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! >
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
