Database Index when SQL statement includes "IN" clause

Question

I have SQL statement which takes really a lot of time to execute and I really had to improve it somehow.

select * from table where ID=1 and GROUP in
(select group from groupteam where 
department= 'marketing' )

My question is if I should create index on columns ID and GROUP would it help? Or if not should I create index on second table on column DEPARTMENT? Or I should create two indexes for both tables?

First table has 249003. Second table has in total 900 rows while query in that table returns only 2 rows. That is why I am surprised that response is so slow.

Thank you

Answer 1

You can also use EXISTS, depending on your database like so:

select * from table t
where id = 1
and exists (
    select 1 from groupteam
    where   department = 'marketing'
        and group = t.group
)

• Create a composite index on individual indexes on groupteam's department and group
• Create a composite index or individual indexes on table's id and group

Do an explain/analyze depending on your database to review how indexes are being used by your database engine.