Alter index vs new index performance

kanha · New User Joined: 05 Nov 2005 Posts: 28

To tune a program, I had to add extra columns to 2 of the existing indexes in 2 different tables. I altered the existing indexes & the performance of the program is improved by 50-60%.

However, when I am creating new indexes not to hamper some of the existing application performance, the performance is not improved, rather detoriarated.

EXISTING:Index1 of Table1:
COL1 CHAR(4) ASC

Index1 of table2:
COL1 TIMESTAMP ASC

ALTER INDEX:Index1 of Table1:
COL1 CHAR(4) ASC
COL2 CHAR(8) ASC

Index1 of table2:
COL2 CHAR(8) ASC
COL1 TIMESTAMP ASC

NEW INDEX:Index2 of Table1:
COL1 CHAR(4) ASC
COL2 CHAR(8) ASC

Index2 of table2:
COL2 CHAR(8) ASC
COL1 TIMESTAMP ASC

Can anyone throw some light why this could occur.

dick scherrer · Posted: Mon Oct 31, 2011 9:20 pm

Hello,

When you add additional keys, you add additional overhead.

How many keys to create and how they are defined are important considerations. There is no generic "right way" - the number of inserts/deletes and the ways the table will be queried must be considered.

sushanth bobby · Posted: Mon Oct 31, 2011 9:43 pm

Kanha,

To analyze or even just to look into it, you have provide some SQL query .

Tell us,
After creating new indexes, did the query use the new index ?
And after droping the new index, did the query use the old index and did you get the same performance you got earlier ?

Thanks,
Sushanth

kanha · New User Joined: 05 Nov 2005 Posts: 28

Yes, the query is using the new indexes.

after dropping the new indexes, the query is using the old indexes & also performing as earlier.

Can't post the query beacuse of security issues. It's a inner join between 2 tables with WHERE clause

enrico-sorichetti · Posted: Tue Nov 01, 2011 2:44 pm