Simon, Clemens, > Do you ever depend on any indexing on the "Text" column which is not COLLATE > NOCASE ASC ? If not, then you should be able to speed up your search by > defining the column the way you think of it. So in your table definition use > > "Text" TEXT COLLATE NOCASE ASC,
We?re just checking this out and we?re getting an error on creating the table. We?ve just checked the SQlite spec (https://www.sqlite.org/syntax/column-constraint.html <https://www.sqlite.org/syntax/column-constraint.html>) and can?t see how to add ASC to it unless we create the Text field as a primary key which is a significant coding change for us. CREATE TABLE "RAG" ( "Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "Count" integer NOT NULL DEFAULT 0, "Text" TEXT COLLATE NOCASE , "Peak" integer, "Calculation" integer NOT NULL DEFAULT 0, "Red" integer DEFAULT 0, "Amber" integer DEFAULT 0, "Green" integer DEFAULT 0, "BayesAttributes" TEXT ); works and CREATE TABLE "RAG" ( "Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "Count" integer NOT NULL DEFAULT 0, "Text" TEXT COLLATE NOCASE ASC , <?? THIS IS THE ONLY DIFFERENCE "Peak" integer, "Calculation" integer NOT NULL DEFAULT 0, "Red" integer DEFAULT 0, "Amber" integer DEFAULT 0, "Green" integer DEFAULT 0, "BayesAttributes" TEXT ); fails. We don?t think adding ASC is allowable unless its the primary key, have we misunderstood both you and Clemens reply to us about this? Rob
