On Fri, May 13, 2016 at 5:23 PM, Clemens Ladisch <clemens at ladisch.de> wrote: > Dominique Devienne wrote: >> select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) >> = upper(u2.user) and u1.user <> u2.user; >> [...] but they are slow (almost 3s, with close to 4M steps, i.e. O(N^2)) > > You can get O(N * log N) with a proper index, and avoiding the function: > > CREATE INDEX u_nocase ON os_users(user COLLATE NOCASE); > > SELECT u1.user, u2.user > FROM os_users u1 > JOIN os_users u2 ON u1.user COLLATE NOCASE = u2.user COLLATE NOCASE > AND u1.user <> u2.user;
This works great, in 3.5ms, 1/2 the time of the group by + group_concat. But it still returns both rows. Any idea on that part? > With a recent enough SQLite, you can use an expression index without > changing the queries: CREATE INDEX u_upper ON os_users(upper(user)); My SQLiteSpy version is too old, and latest is only at 3.8.11, so I'll try it at the command line later. Thanks for the help Clemens. --DD
