The reasons to delete indexes
- Lower memory consumption : each index uses memory. Lots of indexes on large tables means a lot of memory being used
- Lower CPU usage : whenever a query updates a table (insert/update/delete), the indexes of that table must be updated as well. The more indexes, the more CPU power is required
The dangers of deleting indexes
- Performance issues : if the index was still in use, queries will slow down, in some cases causing major issues
- Recreating the index might take long : if the table is big (or extremely big) it could take minutes to hours to recreate the index, impacting your application the entire time. Plus, the process of recreating the index adds additional CPU load.
Don’t delete… just yet. Disable instead !
The solution is simple : instead of permanently deleting the index, temporarily disable it, then let your application run for a few weeks before you permanently delete the index. If you do notice performance issues, just enable the index again :
MariaDB
ALTER TABLE MyBigTable ALTER INDEX someIndex IGNORED; -- "NOT IGNORED" to undo this action
MySQL
ALTER TABLE MyBigTable ALTER INDEX someIndex INVISIBLE; -- "VISIBLE" to undo this action
PostgreSQL
update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass;
Microsoft SQL Server and Oracle Server
ALTER INDEX IX_The_Index_You_Want_To_Dsiable ON TheSchema.TheTableName
DISABLE; -- "REBUILD" to undo this action
