5 ms·
> But why bloat your DB with an index and potentially reduce insert performance for something that's almost never needed? Oh, for crying out loud: will you ple
by bartread 7y ago
> But why bloat your DB with an index and potentially reduce insert performance for something that's almost never needed?
Oh, for crying out loud: will you please stop trotting out this tired nonsense.
This may have been a problem sometimes in the 80s and 90s. With any good modern RDBMS, say SQL Server 2017 (but you can pretty much pick any you like the look of[1]), and a half-competent developer and/or DBA, insert performance is almost never a problem when adding an index.
You generally need some extra storage space for the index, but so what?
I have seen, with surprising regularity over the years, tables with no indexes at all where adding a single clustered index (which is the table itself) can massively improve performance. In this scenario no additional storage is required.
[1] I pick SQL Server because I know Microsoft started seriously addressing insert performance, which had been a problem with SQL Server 6.5 and 7, in SQL Server 2000. Nowadays, if you're half-competent, there really is no issue worth talking about. If you're not it is, of course, still possible to do crazy things that don't perform well. All truly powerful programming languages, database systems, et al., will cheerfully hand you the rope you need to hang yourself.
- z3t4 7y agoInstead of carefully investigate which fields should have an index (all fields used for joins) the brute strategy is to index them all :P
- mastry 7y agoIt depends on how much data you have in the table you're adding the index to and how much traffic you're dealing with. I deal with this every day (SQL 2017) and I assure you that adding an index to a large table has noticeable overhead in a heavily used system.
- bartread 7y agoFor sure, but my beef here (as every time I hear this) is that it's taking an exception and turning it into a fear-mongering boogie-man story that paralyses people into inaction when they (often) have serious performance bottlenecks in their database that need to be addressed. So often with performance it's down to the basics not being followed, and often this comes down to the lack of an appropriate index.
- breischl 7y agoI haven't been close to the DB world for a few years, but didn't they start requiring tables to have a clustered index sometime around SQL 2012?
- bartread 7y agoNot as far as I know. We all joined the company in late 2017 and found a couple of absolutely gigantic heaps in our main database as recently as the beginning of 2018 (running on SQL Server 2012 for several years at that time). Adding clustered indexes here had a transformational impact on performance.
- ShinTakuya 7y agoClaiming they have no impact besides storage is also being disingenuous. The more indexes you have, the more noticeably slow inserts have become on some of the tables I've worked with. Of course, this was an inherent issue with the table design (massive table with lots of columns and a lot of indexes) but sometimes you can only work with the legacy system you have. In an ideal world with a sanely (not over the top or inadequately) normalised database I'd agree with you. But in my experience there have been times where adding an index for a rarely run query was absolutely the wrong decision.
- bartread 7y agoAnd that's fine, but you obviously did some investigation: what irks me is people trotting out this line about insert performance with no investigation. Of course, if you over-index a table, there can be some impact but in the general case that I've seen over the years that's seldom the problem.
- ShinTakuya 7y agoOh okay, I can agree with you there. But I think as a general rule, if something is done less than once a day and it's already a heavily indexed table you can probably skip the index unless it's a massive or potentially massive table. In that case it may be worth exploring alternatives.