Could an Index cause any damage on srv/app

[Cesar Lozano]

A user is trying to get some data running Crystal but the field she's using as a reference has no index at all and the searching takes long time to display the results.
We want to set an index for this field (Table: itsm_ser_custom_fields, Field: scf_scdate1), Could this cause any harm to our server and/or application?.

[Steven E. Protter]

Well, if you don't have the disk space for the index you wish to add, your application will grind to a halt when you add it.

If you have the disk space for the index there will obviously be some disk i/o on the index but you might find performance actually improves.

SEP

[A. Clay Stephenson]

In general, the answer to your question is no. You will, add some overhead to insertions and updates but that should be minimal. Depending upon what database you are using and the underlying B-trees there is one aspect of indices of which you should be aware. Values that are highly duplicate (ie, if the scf_scdate1 column has many, many identical values) can cause performance problems especially on delete operations. You should note that an update of this column triggers a delete and an insertion. Some implementations put the duplicate entries in a linked-list and the list has to be traversed until the matching rowid is found. If you experience this kind of performance hit on deletions, a common workaround is to create a composite index that will be unique (ie your scf_scdate1 column and rowid). In this case, there would be no linked list and all operations would occur in the B-trees directly -- at the expense of additional disk space. The selects should still be smart enough to use the most significant part of the index. Of course, if this field is seldom updated, then the highly duplicate problem can be ignored.