Best Practices: Indexing and interactive filters - Support and Troubleshooting

Best Practices: Indexing and interactive filters Description Interactive filters enable you to filter report widgets directly from a homepage or Performance Analytics dashboard without modifying the reports. You can create an interactive filter and add it to a homepage or Performance Analytics dashboard as a widget. Selecting a value in the Interactive Filter widget filters the data in report widgets on the homepage or dashboard.

Use interactive filters to reduce the number of reports your users create by giving them the ability to manipulate data from an existing report on a convenient dashboard, thus reducing the necessity for redundant reports.

Interactive filters can cause performance issues if they are not supported by a correctly formed index.

Combining indexes and interactive filters The basis for good performance on reports with interactive filters is that the base report is correctly indexed. For example, suppose you are querying on the base report for active=true and assigned_to is null with filters available on state, assignment group, and open time. To start, you would need an index for active and assigned_to for the incident (task) table, which will help narrow down the result set for the report sufficiently. When the interactive filters are activated, they can still rely on the index for active and assigned_to. If users are frequently using these filters, you can modify the existing index of active, assigned_to to contain the following filter fields:

active assigned_to state assignment_group sys_created_on Try to limit the number of items per index to no more than five for usability. Make sure to keep the possible filter fields together in one index. Although MySQL can merge indexes, that is less efficient than having one ready-made index.

Determining whether to include interactive filters in indexes Use interactive filter fields in your indexes in the following circumstances:

The indexes are frequently used. The index is not too large. (The key length of an index is important: Don’t let indexes grow too large.) Do not use an interactive filter in the following circumstances:

When the original report already has the data narrowed down sufficiently, adding more fields to the index does not add significant performance improvements. Do not “oversize” your indexes or “overindex” your tables. The maximum amount of indexes per table should be 64. Index frequently used queries rather than indexing everything. Indexing everything does not result in better performance and will hit or exceed the index maximum. Do not use interactive filters on non-indexed fields.

Interactive filters should be additions to existing reports that should already be indexed. Having indexed fields on interactive filters might help, but only if the originating data is large. Troubleshooting interactive filter performance If a customer reports slow performance when clicking on an interactive filter, troubleshoot the issue as follows:

Run with Debug SQL to see the debug statements issued. Check the slow query log for an entry that might match the SQL statement. Run an explain on the SQL query to see whether an appropriate index is already used or needs to be created. You can determine whether performance issues due to interactive filters have been resolved by checking the following items:

Whether the slow query log shows improvement Whether the debug SQL sql statement returns Additional Information For more information, see the following resources:

To fix interactive filter performance if you created filters on non-indexed fields, create indexes by following the advice in the Community article What index should it be? - OR - Slow Queries explained Product documentation: Interactive filters Community blog: Adding interactive filters to homepages and Performance Analytics dashboards