3 ms·
About indexes, they improve query performance, but they also decrease write performance (and of course take space) that's why it's good idea to remove indices t
by takeda 7y ago
About indexes, they improve query performance, but they also decrease write performance (and of course take space) that's why it's good idea to remove indices that aren't used or rarely used. Especially indices that are placed on columns that have random data and/or are frequently updated (prevents HOT updates) can hurt write performance.
- forcemajeure 7y agoAFAIK it's also proportional to the number of rows with data in the indexed columns. Inserting a row with a NULL in the indexed column does not cause an update to the index (at least experimentally it doesn't seem to contribute to index size on SQL Server). I imagine this is/was a simple optimization for DBMS to implement, but I would love to be corrected otherwise...
- philliphaydon 7y agoIf you change a value to null it’s still a row allocated in the index. The index size won’t shrink unless you rebuild it. Removing and deleting data will just end up with fragmentation.
- danmaz74 7y agoOne very powerful "trick" is to use partial indexes where useful. For example, a pretty common pattern is to have tables where most rows aren't actively used most of the time, eg when you have a status column, and most rows are in the final status which you usually don't touch, so most where conditions include statuses which are not the final one. If you create a partial index WHERE status IN ('s1', 's2' ...), the index becomes a lot smaller and a lot faster.
- james_s_tayler 7y agoI never thought about that, but that's a neat trick.