Simon Slavin wrote:
> On 2 Jul 2015, at 11:16pm, Rob Willett <rob.sqlite at robertwillett.com>
> wrote:
>> ?select Id,Calculation,Peak,Red,Amber,Green from RAG where text = ??
>>
>> CREATE TABLE "RAG" (
>> ...
>> "Text" TEXT,
>> ...
>> );
>>
>> It has four indexes on it
>>
>> CREATE UNIQUE INDEX "RAG_Idx1" ON RAG ("Text" COLLATE NOCASE ASC);
>
> 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,
This is correct.
> You may find that this immediately speeds up the search. Or you may find
> that you may have to change your SELECT to
>
> SELECT Id,Calculation,Peak,Red,Amber,Green FROM RAG WHERE text = ? ORDER BY
> Text COLLATE NOCASE ASC
This would not help; the lookup on the text is independent of the sorting.
To make the lookup use NOCASE, both operands of the = need to have the same
collation:
SELECT ... FROM RAG WHERE text = ? COLLATE NOCASE
Regards,
Clemens
