How do I work with Technical Support to analyze and implement database indexes for my site?

Environment:

Oracle B2C Service, All product versions 

Resolution:

Database indexes can improve query performance and can be used to avoid problems with 'too many rows examined' responses on report runs and API calls used to return data. While an index can improve the speed of data retrieval, it does come at the cost of additional writes and storage space to maintain the index. Specifying too many indexes on a table can actually degrade performance rather than improve it. 

Custom field indexes can be added and viewed from the console in the custom fields or custom objects designer while indexes on standard fields can be viewed in the data dictionary. In the event you need to add an index on a standard field, a service request can be submitted to Ask Technical Support. In doing so, please provide detailed steps to reproduce a query in question. Further, please provide concise reasoning why you believe the query is an issue. In most cases, the query can be more effectively optimized by adding additional filters on already indexed fields as opposed to indexing new fields. 

Once Technical Support has this information they will analyze the data and potentially add the standard field index if deemed appropriate. For more information on the impact of indexing custom fields and guidelines on when to index custom fields please review the answers from our knowledgebase below.

Answer ID 1266: Impact of indexing a custom field
Answer ID 4494: When to index a custom field