Dennis,
How does the index table looks?
Assume the main table to be:
CREATE TABLE table1 (a INTEGER, b INTEGER)
Assume there is an index on column a:
CREATE INDEX index1 ON table1 (a);
Now let's suppose the entries in table1 be:
10, 91
9, 56
89, 78
34, 12
99, 26
19, 77
44, 62
59, 55
Corresponding to this table1 how will index table be?
If each data value was unique, then one index lookup would find the
matching record. Can you explain how this is? Doesn't it will do binary
search on index table?
Regards,
Phani
-----Original Message-----
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 24, 2007 4:06 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] An explanation?
Marco Bambini wrote:
>
> Database is uniformly distributed, I created it ad hoc just for my
> test (sqlite 3.3.12):
Marco,
Another way to think of this is that if your database contained random
numbers in the range 1-100 for both a and b, then an index on either of
those values would allow sqlite to ignore all but the requested value,
or 99% of the entries. It would only have to examine 1% of the records
and would run in perhaps 2% of the time of a full table scan. If your
data had even more distinct values, things would be even faster.
Ultimately, if each data value was unique, then one index lookup would
find the matching record, and the lookup time would only be about
2/300,000 or 0.0007% of the time for a full table scan.
Indexes are not a magical cure all, they only speed up lookups if you
enough different values to let them to reduce the search space to a
small enough portion of the entire database to pay for their overhead.
Dennis Cote
------------------------------------------------------------------------
-----
To unsubscribe, send email to [EMAIL PROTECTED]
------------------------------------------------------------------------
-----
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
