3 ms·
I think that in a lot of cases developers put off creating indices of any sort until a performance problem materializes. Which is perfectly fine.
by rhizome 15y ago
I think that in a lot of cases developers put off creating indices of any sort until a performance problem materializes.
Which is perfectly fine.
- cleaver 15y agoAgreed. And certainly better than creating bad indices.
- glhaynes 15y agoI wonder: are there any good rules-of-thumb for when to go ahead and add indices at table-creation time?
- emmett 15y agoProjected usage. It can be done, but you have to be an expert basically.
- BrentOzar 15y agoAdd an index to support foreign key relationships. If you've got a SalesHeader table and a SalesDetail table, you want an index on SalesDetail.SalesHeaderID, especially if you allow cascading deletes from the SalesHeader table.
- junegunn 15y agoNot perfectly fine if you use MySQL. Adding an index to a table on MySQL locks the entire table, which can introduce hours of downtime if the size of the table is large.
- dspillett 15y agoThe problem there is two-fold: 1. Not having done proper index analysis in the first place. While you can't get it right 100% of the time if you are expecting tables to grow that large you really should think hard about thsi sort of thing as close to the start as practical. 2. Using a database system that can't perform an online index build without locking the whole table. I know that such an operation needs to aquire some locks during its activity no matter what system you use, which will create performance issues for your live site if you are not able to schedule the index change in a pre-planned "maintainence" downtime (i.e. an application that has significant "high availability" requirements), but requiring a full table lock here seems to be a fault in a system that claims to be "enterprise ready".
- rhizome 15y agoExactly. There's a difference between "putting off indexes" and "waiting until it takes hours to add them." The problem is likely evident well before that point.