4 ms·
One mistake I used to see a lot is not paying attention to indices. It's sort of like the "go big" error in that you just create indices all over the place even
by cleaver 15y ago
One mistake I used to see a lot is not paying attention to indices. It's sort of like the "go big" error in that you just create indices all over the place even if they could never be used. Also, it could be adding columns to the index that won't improve access time, or not paying attention to the order of columns.
In reality, you need to analyse your code and profile your application to see what actually is needed for your indices. Also essential is understanding the overhead that an index creates.
I don't see this as often today, however. I think that in a lot of cases developers put off creating indices of any sort until a performance problem materializes.
- rhizome 15y agoI 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.