Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Influence of number of indexes on a table

Status
Not open for further replies.

greggory

IS-IT--Management
Mar 26, 2005
11
FR
I'm asking myself if there is a limitation about the number of indexes created on a table ?

What kind of influence on the performance may this have.

For example we have a table with 6M records and 15 indexes.
I'm asking myself if it is a good idea to create a new one to speed up a specific transaction or is it useless ?
 
You could have a look into your releasenotes, maybe there is something concerning that. At least in my environment i could not find any limitations concerning the number of indexes. Only the indexsize and number of columns is limited.

Concerning performance: why don't you setup a testszenario and measure the difference?

It is obvious, that a properly constructed index will speed up your query significantly. An additional index has to be managed by the databasesystem and thus creates a certain update-workload when writes are done into that table. You have to find out and decide, if you can notice any major speedimpact in writeoperations.

YOu could also analyse the existing indexes and find out, if there is a way to optimize them. Indexes are evaluated from left to right, so sometimes it is possible to merge indexes, because they both contain certain columns, without having any impact on readoperations.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top