Re: deletes and indexes

John,
> Do indexes speed up deletes?
>
> I am deleting 100000 rows from a table containing 1 million rows and
> it seems faster without the index. The index is created on the column
> used in the delete where clause.
>
> I created the index specifically to speed up the delete, but is my
> thinking incorrect?

The slow part is not finding the row but removing the entry. An index isn't going to speed things up if you want 10% of the rows are covered by it.

Every time you delete a row not only does the table have to be updated but so does all its indices. Adding an index before a delete may well slow things down because the cost of updating the additional index will be higher than the cost of finding the row without using it. If you are hitting 10% of the table a full table scan (i.e. no index) is what you want, so adding an index is not going to help.

If you really want to speed your delete up consider dropping all other non-essential indices before the delete and recreating them afterwords.

The total time might be less than deleting the rows with the indices in place. You'd have to test it and see.

Bear in mind that you create additonal risks and problems by dropping and recreating indices.

You need to benchmark a number of approaches and see what's best for you.

David Rolfe
Orinda Software
Dublin Ireland Received on Thu Jan 27 2005 - 07:08:24 CST