Kurt Welgehausen wrote:
select user_id from person p1, person p2 where p1.user_id<>p2.user_id
and p1.name = p2.name
Your query is fine. It's slow because it's doing a full
scan of p1, and for each row in p1 it's doing a full
scan of p2. That makes the time O(n^2).
It should go much faster if you add an index on name.
You may also need to reverse the order of the predicates
so SQLite will pick the right index -- the optimizer may
handle it correctly anyway, but I'm not sure.
Regards
I added an index on the name column and the query ran instantaneously.
I didn't need to change predicate order either. Thx!
