And how do you keep them in sync between test and production environments?
When it comes to database table indexes, my philosophy is that they are an integral part of writing any code that queries the database. You cannot enter new queries or modify a query without analyzing the effect on indexes.
Therefore, I try to keep my indexes synchronized between all of my environments, but to be honest, I'm not very good at automating this. This is a kind of random, manual process.
I periodically analyze index statistics and delete unnecessary indexes. I usually do this by creating a delete script, which I then copy back to other environments.
But in some places indexes are created and deleted outside the normal process, and it is very difficult to see where the differences are.
I found one that really helps is to use simple, numeric index names, for example
idx_t_01
idx_t_02
where t is the short abbreviation for the table. I find that index maintenance is not possible when I try to get smart with all the columns involved, e.g.
idx_c1_c2_c5_c9_c3_c11_5
It is too difficult to distinguish between such indices.
Does anyone have a really good way to integrate index maintenance into source control and development life cycle?
source
share