Timeline for Is it premature optimization to add database indices?
Current License: CC BY-SA 3.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 25, 2015 at 16:18 | comment | added | supercat | That's certainly true, but in that case 0.1% of table operations be using that key, rather than 90%. My point was that in many cases one will know that at least 90% of operations will be select...where id=@@id, update...where id=@@id, etc. If the table never grows beyond 100 items, an index probably isn't going to help much, but if 90% of operations are of the aforementioned style it won't hurt much either. | |
| Feb 25, 2015 at 1:55 | comment | added | svidgen | The point is, it's tough to know what your query makeup is until typical use cases are run through a stress test (or until you see issues with unexpected user behavior in production). If you have a page that keys off of tablex.fieldy, but it's only hit once for every thousand inserts... The index may result in a net degradation. | |
| Feb 25, 2015 at 1:49 | comment | added | svidgen | @supercat I'm not sure I fully understand your questing. In terms of an active application, almost any increase in the execution time or number of ios has the potential to introduce deadlocks. ... But, more to the point, the presence or absence of an index in most applications is negligible until the database reaches a critical size and/or concurrency level. E.g., when all of your indexes no longer fit in memory ... | |
| Feb 25, 2015 at 1:01 | comment | added | supercat | What sorts of realistic scenarios do you envision that would be consistent with 90% of operations using a column as a key, and where adding an index would cause deadlock? | |
| Feb 25, 2015 at 0:53 | comment | added | svidgen | @supercat "never" ... Until you start seeing deadlocks in your production environment... | |
| Feb 25, 2015 at 0:25 | comment | added | supercat | Spending an extra 30 seconds to do something that's almost certain to improve performance and very unlikely to harm it isn't "premature optimization". If 90% of the operations on a table use a particular column as a key, then either indexing it will improve performance, or performance will never be slow enough to matter, and adding code to create the index may take less time than determining whether it's really necessary. | |
| Feb 24, 2015 at 15:24 | history | answered | svidgen | CC BY-SA 3.0 |