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 Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Max. No of Indexes in a table?
- Thread starter sjose
- Start date
Jose:
I've never seen any informix documentation state a limit for the number of indexes on a table. Obviously, inserts and deletes are slower with a greater number of indexes.
At some point, performance would be prohibitive given enough data and enough indexes.
Regards,
Ed
I've never seen any informix documentation state a limit for the number of indexes on a table. Obviously, inserts and deletes are slower with a greater number of indexes.
At some point, performance would be prohibitive given enough data and enough indexes.
Regards,
Ed
Hi,
In the release documents, it is mentioned Under title limits in Informix Dynamic Server 7.31:
Max Indexes are 77 for 2k page size (roughly double this number for 4k page size) and Max Columns per index are 16.
I agree with olded about the negative impact it creates when you go overboard with index creation and when it comes to maintain the rows in such tables. You will certainly going to gain substantial retrieval speed but at cost of maintenance of such rows. To trade-off between these two extremes, consider only those column(s) for candidature of index creation, should they be part of WHERE clause in several SQLs and applications.
Regards,
Shriyan
In the release documents, it is mentioned Under title limits in Informix Dynamic Server 7.31:
Max Indexes are 77 for 2k page size (roughly double this number for 4k page size) and Max Columns per index are 16.
I agree with olded about the negative impact it creates when you go overboard with index creation and when it comes to maintain the rows in such tables. You will certainly going to gain substantial retrieval speed but at cost of maintenance of such rows. To trade-off between these two extremes, consider only those column(s) for candidature of index creation, should they be part of WHERE clause in several SQLs and applications.
Regards,
Shriyan
- Status
Similar threads
- Locked
- Question