On 17/05/2013 11:06 AM, Richard Hipp wrote:
On Fri, May 17, 2013 at 11:02 AM, GB <gbi...@web.de> wrote:
Richard Hipp schrieb am 17.05.2013 16:37:
Collating orders and affinities might be disqualifying the constraint on
"id" from being used with the index.
It just came to my mind that "id" is an INTEGER PRIMARY KEY column and as
such is part of every index anyway. Could it be that the additional "id"
part of that index is silently ignored in this case?
Yes. If "id" is the INTEGER PRIMARY KEY then that messes up everything.
Don't do that. Change the index to omit the "id" and you'll get better
results.
Furthermore, the "id" is unique so if there is a constraint on the "id" the
query planner will always use that constraint to look up the rows in the
table directly, rather than going through an index, since doing so will be
about twice as fast as using an index.
Could you explain that last bit? I always thought UNIQUE was implemented
under the hood with a regular index. How would simply knowing something
is unique make it so much easier to find the needle in the haystack
without an index?
Do normal indexes map index keys to primary keys, so you still have to
drill the PK index afterward? (BerkeleyDB does that, but it does it for
all indexes, not just user-created ones).
Thanks,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
