Re: [sqlite] Core Data
Nico Williams
<kri...@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...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Igor Tandetnik
> Why doesn't sqlite implement a hash tag on strings itself when there is an index?
Because a hash would only help with conditions of the form "textField = ?", while the kind of b-tree index that SQLite utilizes also works for "textField > ?" and "order by textField".
--
Igor Tandetnik