Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
I have a table with about 37 indexes. Would that be considered too many? What are some best practices regarding indexing?
EDIT: It is warehousing data, updated daily with new transactions. There are 47 million rows. 3 of the indexes are very necessary. The other 34~ are 'where' clause indexes intended to speed up queries.
One more index than you need is too many. One less is too little.
I've tried searching for a case where having too many indexes was a problem and couldn't really find anything
You KNOW you have too many if your inserts are too slow, and the index used for reading are not speeding things up enough to make up for it.
there is no 'magic number'. Every index after 0 slows down insert/updates a bit, but that shouldn't stop you from creating needed indexes. 37 does sound like a lot, make sure you're not using unnecessary indexes. For example, usually if you have an index on columns A, B you don't need a separate index on A. Or if you have an index on A, B, C, D you don't need another index on A, B, C. And you probably don't gain much by having another index on A, B, C, E.
Creating an index is a trade-off. You have to determine whether the overhead of maintaining the index (and thus slowing down DML operations) and the additional space consumed by the index is worth the performance gain you get from the queries that use the index. That, in turn, depends on how much the queries benefit and how critical those queries are.
The number of indexes on a table is irrelevent in this cost-benefit equation-- each index ought to be considered on its own (though the performance benefit of a particular index may be dependent on whether a different index is available for the query to use instead).
Since you have 47 Million rows and your are using the database for reporting, then we are talking about a Data Mart/Warehouse type of application rather than OLTP application.
You need to consider using a Star Schema to start with and you may (based on the features in your choosen database) to build static aggregate tables connected to dimension tables to provide summaries using batch processing. The above will provide you better performance and possibly less indexes. You can gain further performance by shutting off (some) of your indexes during load and re-building them after load is complete. Building an index over 47 million rows is very fast.
Above all, you need to justify the need of every index as a general database design practice. This can only be done by examining, in details, user reporting needs. Remember that even if you define indexes, your query may not use one or more of them, so your user needs, query analysis and good database design should help you determine exactly whether to use an index or not and the type of index required.
I have designed several database and data marts, but honestly, never seen that number of indexes per table.
Always you have to evaluate your write speed /maintenance requirements to read requirements. If you have almost a 100% read ratio to write ratio, and you update the data very infrequently or you even produce "read-only snapshots" of your database and use that for read scaling, fire away on the index front!
I would test all my scenarios very thoroughly on a production like system though, I have heard of scenarios where index adds have slowed down query compilation / updates considerably.
Most things that I have seen say that having more than 5 indexes will begin to slow down your insert speed.
Consider this, every index you have is like having a new table in your database with the same information just sorted a different way. Having 37 indexes is like having to do an insert 38 times!
No rule is going to be a solid number but unless this is some sort of historical record that will never change that may be to many.
