On Wed, Oct 20, 2010 at 9:41 AM, Mathieu Schroeter <schroe...@epsitec.ch>wrote:
> > The first plan looks the best but it does not explain the ORDER BY. > If I could done this job with pencil and paper, my way will be: > > a) enumerate all entries in lol > b) for each entry look up a corresponding row in tmp using its > primary key > c) the loop over lol is complete, I see that tmp.data uses > `COLLATE foobar` then I look if an index exists. > d) Yes then I use idx_foobar instead of many calls on the > comparison function. > Calls to the comparison function are needed in order to access idx_foobar. > > Why not? > This whole question may well be moot. COLLATE only changes TEXT comparison. BLOB values are *always* compared using memcmp(), regardless of what COLLATE you are using. There are no exceptions to this rule. The query planner, however, does not know in advance whether you are using BLOBs or TEXT so it forms its plan based on the assumption that you are comparing TEXT, since (after all) you did specify a COLLATE. > > > The way used by SQLite looks like > > a) enumerate all entries in lol > b) for each entry look up a corresponding row in tmp using its > primary key > c) the loop over lol is complete, there is `COLLATE foobar` then > I call the cmp function... > > > Mathieu > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
