"P Kishor" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > On 10/8/08, James Pringle <[EMAIL PROTECTED]> > wrote: >> SELECT lat, lon, depth FROM hydro WHERE depth>= 49.01 AND depth >> <= 50.99 >> >> It selects and returns the records in 82 seconds. I wished to >> improve on this time, so I created an index with the following >> command: >> >> CREATE INDEX hydro_indx ON hydro (depth) >> >> I then tried the SELECT command again, and read the results into >> memory, and it took 717 seconds!?! > > Most likely because it doesn't have much it can optimize (you note > above that "no specific value matching more than 0.5% of the data" -- > with 32 million rows, that is a lot of variation). > > Think of it like image compression -- if you have too much variation > in your image... imagine the worst case -- every pixel is a different > color. You can't really compress it. It is sort of like that (dunno if > that is a good analogy).
With database indexes, it's the reverse. The index is supposed to work best when all or most values in a column are distinct, and worst when a column has just a few distinct values. The rule of thumb is, when the WHERE clause selects about 10% or more of all the records, the query is better off not using the index but doing a linear table scan. But here, the data seems to have a very good cardinality. >From James' description, the index should have helped. There must be more to the story. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
