Another questions about the database Index...

[fletchsod]

Quick question here!! I have are

  .. FROM SESSIONS WHERE SESSION_ID = '***'			
  .. FROM SESSIONS WHERE SESSION_ID = '***' AND USER_ID = '***'
  .. FROM SESSIONS WHERE USER_ID = '***'

The primary key is SESSION_ID. Since SESSION_ID is automatically indexed due to primary key. Suppose I add the USER_ID to the existing index that include the primary key, would it still work? Or do I have to make a seperate index for "USER_ID"?

Thanks..

 

[blom0344]

If session_id is the primary key, then it would be pretty useless to add user_id to the where clause.
AFAIK you should not mess with the index on the primary key. I should expect that DB2 will not even allow you.
This is good reading:

http://www.dbazine.com/db2/db2-mfarticles/mullins-db2indexing

Ties Blom

 

[fletchsod]

So, I would have to create a seperate index instead for the "USER_ID"?? Right??

 

[blom0344]

Yes, you should do this , but try to test before and after.
If USER_ID has a very low cardinality, then adding an index may have zero effect on performance. As the article points out, there is no golden rule, you need to test and find out..

Ties Blom

 

[fletchsod]

Right. Thanks. Yea, I read the article. I know what you meant about index as a matter of arts.

 

[fletchsod]

If the table have a few indexes then DB2 would go for whatever is the shortest by default? What would the default be that DB2 will use?

I'm just wondering because I have 18 tables and some SQL Queries varies.

 

[blom0344]

The query optimizer has a mind of its own. The sure way of finding out is using explain plan to check how the optimizer chooses its path , then add an index and check again. You can theorize all you want, but in this case you need to test (especially if you already know the queries that are going to be fired at the tables)

Ties Blom