Menu
I recently sped up a complicated query by an order of magnitude by giving SQLite a good index to work with. Results like this make me wonder if I should index a lot of other fields that are commonly used for JOINs or ORDER BY clauses. But I don't want to get overzealous and have it backfire on me: I assume there must be some reasons not to create indices, or every field would be indexed by default.
I'm using SQLite in this case, but of course DBMS-agnostic advice is welcome as well.
292
However, the more indexes you add, the slower your inserts and deletes will go, and the more competition pages will have for precious memory space. You may have to throw hardware at it in the form of more memory and faster storage. Sometimes, even just 5 indexes are too many.
Too many indexes create additional overhead associated with the extra amount of data pages that the Query Optimizer needs to go through. Also, too many indexes require too much space and add to the time it takes to accomplish maintenance tasks.
There is some overhead to an index. The index itself occupies space on disk and memory (when used). So, if space or memory are issues then too many indexes could be a problem. When data is inserted/updated/deleted, then the index needs to be maintained as well as the original data.
A general rule of thumb is that the more indexes you have on a table, the slower INSERT, UPDATE, and DELETE operations will be. This is why adding indexes for performance is a trade off, and must be balanced properly.
Indexes slow down inserts and updates (which can become a really serious issue with locking) and cost disk space. That's pretty much it.
160
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
