On Mon, 2 Sep 2013 13:48:02 +0100 Simon Slavin <slav...@bigfraud.org> wrote:
> > On 2 Sep 2013, at 8:25am, Eduardo Morras <emorr...@yahoo.es> wrote: > > > Or create the index with collate > > > > CREATE INDEX idx_collated_column ON myTable ( column COLLATE NOCASE ) > > The problem with doing it in the index is that it's hard to predict when > SQLite will use a particular index. Better to think about the nature of your > data when you create the table. But if you define it at table/column level, the collate will be applied to all operations that use the column, like group by or may altere other indexes (making "aAa" and "aaa" equal column has less distinct values and index stats aren't real ). Don't know if column collation overrides index collation or viceversa. I see it as one of the reduced list of cases where developer should send hints or suggestions (mafia style) to sqlite to use a defined index, if more than one is defined on same columns with different collation. > > On the other hand if you have already created your table and have lots of > foreign keys it can be a pain to try to reconstruct your data by DROPping and > recreating tables. It may be easier just to add a new index. > > Simon. --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
