Marco Bambini <[EMAIL PROTECTED]> wrote: > Yes, I know that it is faster ... I just wonder why with one index > the query is slower that without any index...
Probably because most of the entries in your table match the term being indexed. In your case, this likely means that a large fraction of the table entries have a=5. When searching from an index, SQLite first finds the index entry, then has to do a binary search for the table entry. The table entry lookup is O(logN) where N is the number of entries in the table. If the number of rows in the result set is proportional to N, then the total runtime is O(NlogN). On the other hand, the total runtime of a full table scan (which is what happens if you omit the index) is O(N). N<NlogN. Usually when you index something, you expect only a constant number of entries in the table with that value. In that case, an indexed lookup is O(logN). A full table scan is still O(N). logN<N. So usually an indexed search works out better. At least that is the assumption that the SQLite optimizer makes in the absence of information to the contrary. Running ANALYZE gives the optimizer more information about the index contents. It might choose a full table scan over an indexed lookup in your case. Have you tried it? -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
