In relational database, Index will be the important mechanism for boosting performance.
An index can be dropped rather simply. Check your particular implementation for the exact syntax, but most major implementations use the DROP command. You should take care when dropping an index because performance might be slowed drastically

DROP INDEX Statement will look like this:

DROP INDEX [INDEX NAME]
ON [TABLE NAME]


EXAMPLE :

Want to drop a INDEX on Table Users which is names user_fname .

SQL Statement:

DROP INDEX user_fname
ON Users