> LIKE is used when comparing strings with wildcards. For example, val LIKE > 'abra%' (which will match 'abraCaDAbra' and 'abrakadee'. > > If there are no wildcards you should be using =, not LIKE. LIKE will/should > always indicate that a table or index scan is required, perhaps of the whole > table/index if the like expression is not a constant (there is no other > choice since > the wildcarded expression could evaluate to '%d%' which would return every > row with a 'd' anywhere in the value. This means that the query planner must > assume that this join will require a full table/index scan for each > inner-loop and > may return all rows because no other plan assumption would be valid. This > will > result in really crappy performance. > > Are the columns declared as COLLATE NOCASE, or just the index? If just the > index, why?
Was just the index as I didn't know better, but its corrected now. > If there is some (really strange) reason why the table column is not declared > with COLLATE NOCASE, then you can always override the collation of the > column in the expression itself: > > CollateBinaryColumn COLLATE NOCASE = > SomeOtherColumnCollationDoesNotMatter This insight is much appreciated, thanks! jlc _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
