CODEWITHSUNDEEP
databaseindexingdb2where-indatabase-indexes
Veljko
Veljko

Reputation: 1808

Database Index when SQL statement includes "IN" clause

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

Upvotes: 2

Views: 709

Answers (2)

zedfoxus
zedfoxus

Reputation: 37069

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.

Upvotes: 2

Paul Grimshaw
Paul Grimshaw

Reputation: 21034

Try a join instead:

select * from table t
JOIN groupteam gt
ON d.group = gt.group
where ID=1 AND gt.department= 'marketing'

Index on table group and id column and table groupteam group column would help too.

Upvotes: 0

Related Questions