CODEWITHSUNDEEP
sqlindexingdb2database-indexes
Veljko
Veljko

Reputation: 1808

How to create database INDEX for SQL expression?

I am beginner with indexes. I want to create index for this SQL expression which takes too much time to execute so I would like on what exact columns should I create index? I am using DB2 db but never mind I think that question is very general.

My SQL expression is:

select * from incident  where (relatedtoglobal=1) 
and globalticketid in (select ticketid from INCIDENT where status='RESOLVED')
     and statusdate <='2012-10-09 12:12:12'

Should I create index with this 5 columns or how?

Thanks

Upvotes: 0

Views: 669

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115660

Your query:

select * 
from incident  
where relatedtoglobal = 1 
  and globalticketid in ( select ticketid 
                          from INCIDENT 
                          where status='RESOLVED'
                        )
  and statusdate <='2012-10-09 12:12:12' ;

And the subquery inside:

select ticketid 
from INCIDENT 
where status='RESOLVED'

  • An index on (status, ticketid) will certainly help efficiency of the subquery evaluation and thus of the query.

  • For the query, besides the previous index, you'll need one more index. The (relatedtoglobal, globalticketid) may be sufficient.

I'm not sure if a more complex indexing would/could be used by the DB2 engine.

  • Like one on (relatedtoglobal, globalticketid) INCLUDE (statusdate) or

  • Two indices, one on (relatedtoglobal, globalticketid) and one on (relatedtoglobal, statusdate)

The DB2 documentation is not an easy read but has many details. Start with CREATE INDEX statement and Implementing Indexes.

Upvotes: 2

Related Questions