2

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.

|improve this question
  • Kind of an open ended question, and you've got two separate questions in there anyway. – Michael K Mar 22 '12 at 20:37
  • I've tried searching for a case where having too many indexes was a problem and couldn't really find anything. – jeremylaw Mar 22 '12 at 20:42
  • 3
    I think you might want to look into normalizing that table a bit more – Ryathal Mar 22 '12 at 20:45
  • 3
    37 indexes is a database design smell. If they all make sense, the table probably doesn't. – user281377 Mar 22 '12 at 20:55
  • 2
    @dunsmoreb both indexes and indices are correct ;) – Songo May 21 '12 at 11:05
21

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.

|improve this answer
7

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.

|improve this answer
4

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).

|improve this answer
2

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.

|improve this answer
1

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.

|improve this answer
0

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.

|improve this answer
  • The table will be getting populated everyday so it looks like I added too many indexes. – jeremylaw Mar 22 '12 at 20:44
  • 1
    1 index will slow down the speed of inserts. 2 indexes will add twice the overhead of 1 index (assuming roughly the same proportion of non-NULL columns). It makes little sense to pick any particular number of indexes as any sort of cut-off point. – Justin Cave Mar 22 '12 at 20:48
  • @JustinCave This is correct, but having a guideline of some sort can be very helpful. – JustinDoesWork Mar 22 '12 at 20:51
  • @JustinDoesWork - A guideline that provides useful guidance is helpful. I can't see how picking any number provides useful guidance, however. The overhead of index maintenance scales linearly with the number of indexes so each index ought to be considered on its own terms regardless of the number of other indexes on the table. There are plenty of tables that need substantially more than 5 indexes. There are plenty of tables that need substantially fewer than 5 indexes. I can't see any reason to prefer a cutoff of 5 rather than 3 or 7 or most any other number. – Justin Cave Mar 22 '12 at 20:57
  • @JustinCave I think the fact that I gave a number is being over analyzed, it isn't a hard cutoff or anything but it does give you an idea of what is reasonable. No where in my answer do I say anything over 5 is not ok, just that you begin to see noticeable slowdowns after 5. – JustinDoesWork Mar 22 '12 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.