Thanks for feedback. "The right data"::: we don't have any wrong data.... Imagine to have a table with millions of records, for example records about website, with a field="needtoupdate=false" normally. A background job that mark few records at time by setting "needtoupdate=true". And another background job that search records to be updated, and turn back to "needtoupdate=false". He need to localize them with a "where needtoupdate = true". Actually if a do an "analyze" in a moment when all "needtoupdate" are false, a simple "select count(*) from mytable where needtoupdate = true" will do a full-table-scan over millions of records. I'm not sure, but i think the index contain the info "there isn't record with value 'true'", right?
Actually, i use with success the new syntax "INDEXED BY", but Sqlite docs say that is not a right solution. My next solution maybe only (if nobody can help me) use a datetime and not a boolean, but i'm searching another solution because require more useless disk-space only for this problem. Bye > Clodo wrote: > >> Many thanks, it's a good news that resolve my problem. >> >> But still remain "a trick", i think the behaviour descripted in my >> original feedback is "strange".. i understand, if all fields have the >> same value, an index on that have a zero "height" in computing the best >> indexes to use, but not use index at all and do a full-table-scan, for >> what i understand about sqlite, imho is strange... >> >> > If an index is useless for the query, then a full table scan will > generally be quicker than an indexed scan. > > An indexed scan requires accessing two things (the index plus the data) > and also traversing the index which is more 'random access' than > sequentially scanning through the table. > > If the analyse has analysed the right data, then letting it do the table > scan is probably the best thing. If it hasn't, then put the right data > in the table before doing the analyse... > > (PostgreSQL does the same thing, if the index is useless, or the > database clustering is highly correlated with the index, then it won't > use the index at all). > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
