What does the COLLATE keyword do when creating a sqlite index?

It is the way that the sql engine orders data internally. Binary Collation does what it suggests, it does a binary comparison. Generally its the fastest collation though I have never quantified it, as it checks bit patterns, which means it is case and accent sensitive.

Binary collation compares your string byte by byte, as in an unicode table. For example: A,B,a,b. A case insensitive (NOCASE) order would be: a,A,b,B.

The advantage of binary collation is its speed, as string comparison is very simple/fast. In the general case, indexes with binary might not produce expected results for sort; however, for exact matches they can be useful.

COLLATE NOCASE also affects case sensitive queries.

If you have a column with these values: 'aa', 'aA'

select * from table where col = 'aa'

If you have created your column with COLLATE NOCASE it will return both 'aa' and 'aA'. Otherwise, if you didn't specify it, it will return only 'aa'.

You can also specify it in a query (this is slower then if you had created your column with COLLATE NOCASE)

select * from table where col = 'aa' COLLATE NOCASE