2

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

Veljko
  • 1,708
  • 12
  • 40
  • 80
  • That is not a lot of records for a database to handle. In general, yes, an index will help. What type of database? – Neo Dec 08 '16 at 16:07
  • Yes you need an index on ID and GROUP. Or Select the records into a Temp Table first. Select into #tmpGroups from groupteam where department ='marketing' Then join that temptable to your table in select – Scott Dobbins Dec 08 '16 at 16:09
  • hi all db2 database is used – Veljko Dec 08 '16 at 16:13
  • @Dejan does any of the answer below suit your question? – zedfoxus Dec 12 '16 at 05:50
  • @zedfoxus Hi, when I change my query to yours with exist do you think I could additionally have better performances if I create index with columns department and group? Thanks – Veljko Dec 12 '16 at 18:10
  • Yes, I like to believe that your query can perform better. You should use EXPLAIN/VISUAL EXPLAIN along with testing done test cases to confirm. – zedfoxus Dec 12 '16 at 21:34
  • @zedfoxus Hi I marked it as answer. Although i have really big doubt if I should create this index which I asked you because only 900 rows exists in this groupteam table for which I am making this join. I suppose because of this low number I do not have to create index? Or regardless of that low number I should create index because I am basically doing the join from the main TABLE. I am not quite sure for this so please if you can explain me in more details what is your opinion? Thanks – Veljko Dec 13 '16 at 02:29
  • I'd recommend you add an index even though you have only 900 records. When you create an index on department and group, and when you execute this query multiple times, DB2 is likely to cache the index in memory giving you improved performance. – zedfoxus Dec 13 '16 at 03:56

2 Answers2

2

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.

zedfoxus
  • 35,121
  • 5
  • 64
  • 63
  • i agree, but i prefert and exists (select * .... The use of the * is more readable according to my. and not better in performance (its a myth) http://stackoverflow.com/questions/6137433/where-does-the-practice-exists-select-1-from-come-from – Esperento57 Dec 08 '16 at 18:27
  • 1
    That's a very nice link you shared. Thank you, @Esperento57. – zedfoxus Dec 08 '16 at 19:44
0

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.

Paul Grimshaw
  • 19,894
  • 6
  • 40
  • 59