CODEWITHSUNDEEP
sqlsubqueryquery-optimizationwhere-clause
SoH
SoH

Reputation: 49

How to speed up sql query execution?

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).

Upvotes: 0

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

ids are usually unique. Is it sufficient to do this?

select x.*
from x
where id in ( . . . );

You would want an index on id, if it is not already the primary key of the table.

Upvotes: 1

GMB
GMB

Reputation: 222412

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.

Upvotes: 1

Related Questions