The task is to execute the sql query:
select * from x where user in (select user from x where id = '1')
The subquery contains about 1000 id so it takes a long time. Maybe this question was already there, but how can I speed it up? (if it is possible to speed up please write for PL SQL and T-SQL or at least one of them).
Advertisement
Answer
I would start by rewriting the in
condition to exists
:
select * from x where exists (select 1 from x x1 where x.user = x.user and x1.id = 1)
Then, consider an index on x(user, id)
– or x(id, user)
(you can try both and see if one offers better improvement that the other).
Another possibility is to use window functions:
select * from ( select x.*, max(case when id = 1 then 1 else 0 end) over(partition by user) flag from x ) x where flag = 1
This might, or might not, perform better than the not exists
solution, depending on various factors.