On Fri, Mar 23, 2012 at 11:26 AM, Kristof Van Landschoot <kris...@coin-c.com> wrote: > Not sure if this question belongs here, let me know if it doesn't. It > can also be answered here > http://stackoverflow.com/questions/9808284/why-does-manually-implementing-a-hash-tag-give-a-performance-boost-to-my-queries > and I'll cross post relevant info to stackoverflow if I receive it. > > I have a Core Data app on iOS and what I noticed is that I get a > significant performance boost when querying on hash tags (integers) as > compared to when querying on strings, even though in both cases I use > an index on the field. > > Is this expected behavior? Why doesn't sqlite implement a hash tag on > strings itself when there is an index? > > I'm mainly asking because I think there might be something I am overlooking.
I assume the hash column is NOT an INTEGER PRIMARY KEY because you have to allow for collisions. If the hash column were an INTEGER PRIMARY KEY than you would have a single b-tree lookup when for lookups by hash, as opposed to two b-tree lookups if the key were not an INTEGER PRIMARY KEY... unless the index for lookup by name were a covering index. But since you have to allow for hash collisions, so the hash column can't be an INTEGER PRIMARY KEY. Two possibilities come to mind: a) you have a covering index for lookups by hash but not by name, b) lookups by hash require fewer I/Os than lookup by name because hash values encode significantly smaller than the names. You should EXPLAIN QUERY PLAN for both queries. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
