Re: Pizza Example

>
> > In a system I worked on a couple of years ago, a DBA was asked
> by a user
> > to add an index to a column with very low cardinality (it was a flag
> > which contained the values 0 and 1 only). It seemed a harmless change,
> > so he did it. A queries which had been taking a few milliseconds began
> > taking around 5 minutes to complete. This query was executed, once per
> > call, on an overnight data-collection system, connecting to 12,000
> > remote terminals via dialup. Calls normally took around 20 seconds to
> > complete, with a 10-second "no data" timeout. Needless to say, we didn't
> > collect any data until that was found and fixed.
> >
>
> I should hope that the DBA was chastised and, depending on his/her
> experience, sent out for some training or fired.
>

Why ?

(I do not see something principal wrong with adding an index to a column with low cardinality. But it should be taken into account that the index has to be maintained during mutations).

(Second I think that adding an index to a RDBMS should not make queries slower. An optimizer might make a 'wrong' assumption even based on good statistics, but if an optimizer uses an index were it clearly should not be used I do not thing the DBA should be chastised for it.)

Should a DBA alter the production system in request to a User query ? Or should all the changes be tested and accepted in a 'test'-system ?

If a DBA makes a change on a production system, then he can only be blamed for a mistake if the normal procedure is to go through rigorious testing and he ommitted that.

I do not agree with your assumption or judgement that the DBA did make a mistake. If it is his job to do things on the fly, as in this case it seems to me, than we should not blame him that once in a while he misjudges the impact of a change. If there is low cardinality on a column, there still CAN be sufficient reason to have an index on that column. And the performance should not suffer if only an index is added, the system does not have to use the index.

ben brugman Received on Thu Apr 08 2004 - 14:20:49 CEST