> I am using LIKE as the columns are indexed NOCASE and I need the > comparison case insensitive. I suspect this is where is breaks down > but I don't know enough sql to really appreciate the ways I could > approach this better.
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? 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
