On 10/8/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "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.
Interesting. If I stare at your explanation long enough, it starts to make sense. Then I quickly reverse back to my own (faulty) logic (I am assuming that stuff that comes from Igor has a 100% chance of being correct as opposed to stuff that comes from me). Imagine a db where the column 'foo' that you are querying against has a different value for every single row. What possible good an index would do? The db would spend as much time looking through an index, and then going back and looking through the db... well, ok, once it has found the matching entry in the index, it would jump to the right row in the db, but it won't gain anything. I guess I should go back to reading some db indexing theory here. > > 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
