Statistics view performance issues with large datasets

Problem/Motivation

The webform_statistics view experiences severe performance issues when dealing with large numbers of webform submissions (300k+). The view can take excessive time to load (30+ seconds) and may result in 500 errors when accessing statistics pages.

Root Causes:

• Missing database indexes: No indexes on created and langcode columns, causing full table scans
• No default filters: Initial page load queries all submissions without date restrictions
• Inefficient aggregation: Using AVG() instead of MAX() for date sorting in grouped queries
• Excessive cache overhead: Tag-based caching adds overhead with large datasets

Performance Impact:

Database query analysis with 377,485 submissions:

  EXPLAIN SELECT webform_id, langcode, FROM_UNIXTIME(created, '%Y-%m-%d'), COUNT(*)
  FROM webform_submission
  GROUP BY webform_id, langcode, FROM_UNIXTIME(created, '%Y-%m-%d');

  Result: Full table scan | 377,485 rows | Using temporary; Using filesort
  

Steps to reproduce

1. Install webform_statistics module
2. Create multiple webforms in different languages
3. Accumulate 300,000+ webform submissions across webforms and languages
4. Navigate to /admin/structure/webform/submissions/statistics
5. Navigate to /admin/structure/webform/submissions/statistics_day
6. Observe slow page load times (30+ seconds) or PHP timeout/500 errors
7. Check database slow query log to confirm full table scans

Proposed resolution

1. Add Database Indexes (Update Hook)

Create update hook webform_statistics_update to add three indexes:

• idx_created on webform_submission.created
• idx_langcode on webform_submission.langcode
• idx_webform_lang_created composite index on (webform_id, langcode, created)

2. Optimize View Configuration

Update config/install/views.view.webform_statistics.yml:

• Add default created_from filter value: -90 days with type offset
• Change sort group_type from avg to max
• Change sort order from ASC to DESC
• Change cache type from tag to none
• Add header text informing users about the default date filter

Expected Performance Improvement:

Remaining tasks

1. Review and test patch with large datasets (>100k submissions)
2. Verify index creation works on different database backends (MySQL, PostgreSQL, SQLite)
3. Test that existing sites with indexes don't error on update hook
4. Confirm all view displays inherit optimizations (by_day, by_week, by_month, charts)
5. Validate that users can still clear the default date filter to see all submissions
6. Create release with update notes about performance improvements

User interface changes

Added Elements:

• Header message: Warning notification displayed at top of statistics pages: "By default, this view shows submissions from the last 90 days. Use the filters below to adjust the date range or filter by specific webforms."

Modified Behavior:

• Default date filter: Statistics views now default to showing submissions from last 90 days instead of all time
• Sort order: Results now sorted by most recent submissions first (DESC) instead of oldest first (ASC)
• Filter behavior: Users can clear the "Date from" filter to view all submissions if needed

No Changes To:

• Exposed filter options and controls
• Chart displays and visualizations
• Field labels and formatting
• Permission requirements

API changes

None. This patch does not introduce any API changes. All changes are internal optimizations to database queries and view configuration.

Data model changes

Database Indexes Added:

Three new indexes added to the webform_submission table (managed by the webform module):

Schema Impact:

• No data migration required: Indexes only affect query performance, not data structure
• Storage overhead: Minimal (~5-10% of table size for index storage)
• Write performance: Negligible impact on insert/update operations for webform submissions
• Backwards compatible: Sites without the indexes will continue to function (just slower)
• Rollback safe: Indexes can be safely dropped if needed without data loss

Update Hook:

  function webform_statistics_update_*()
  

Creates indexes with existence checks to prevent errors on re-run or if indexes already exist.