[Performance] Add an index on webform_submission_data name and property

I am open to any performance improvements.

The patch would add the index to new installations and provide an update hook for existing installations.

Sorry I didn't followup on this. It did dramatically improve performance in our environment, particularly on a crucial operation, and it did not cause any noticeable problems with webform inserts.

I'm having performance issues with certain webform queries as well. The webform_submission_data table comprises nearly 24% of one database, taking up over 28M, though I'm sure greggles' is larger.

I'm using webform views (see this performance issue 🐛 Reverse Entity Webform Submission Relationship is slow on MariaDB <= 10.5 Active ), and adding submission fields into the view means that the webform_submission_data table is joined once for every field.

I tried adding an index, and using ANALYZE on the query I'm testing shows that the number of rows improves dramatically.

Before:

• select_type: SIMPLE
• table: webform_submission_data
• type: ALL
• possible_keys: webform_id
• key: \N
• key_len: \N
• ref: \N
• rows: 159725
• r_rows: 154624
• filtered: 15.86
• r_filtered: 0
• Extra: Using where; Using join buffer (flat, BNL join)

Afterward:

• select_type: SIMPLE
• table: webform_submission_data
• type: ref
• possible_keys: webform_id,ix_webform_submission_data_name_property
• key: ix_webform_submission_data_name_property
• key_len: 514
• ref: const
• rows: 5214
• r_rows: 5214.00
• filtered: 15.86
• r_filtered: 0.03
• Extra: Using where

Adding the index improves the query execution time quite a bit as well.

I would also like to mention that if we add a patch/MR, it maybe should check if the index already exists, because someone might have created it like greggles did.

I'm new to adding indexed and how Drupal does this. I copied the examples I could find in existing code. Please review.

Note that I didn't use the same index name that was suggested in the workaround, which used

create index ix_webform_submission_data_name_property on webform_submission_data (`name`, `property`);

The new index is name_property and is essentially added with the Drupal DB abstraction layer version of:

ALTER TABLE webform_submission_data ADD INDEX name_property (`name`, `property`);

You can see existing indexes on the table with SHOW INDEXES FROM webform_submission_data;

You can drop the workaround index with DROP INDEX ix_webform_submission_data_name_property ON webform_submission_data;

I changed the hook to webform_update_9600, because it wasn't running. The module currently supports a minimum of Drupal 9.4, and the module version is 6.2.x, so N is 9600.

I tried including delta in the index as well, but I didn't see any difference/benefit. Does anyone think that should be included, or is the MR good as-is?

As a note, I think documentation should be added to say that the following could greatly improve SQL query performance.

Put in my.cnf on the DB server, or in .ddev/mysql/my.cnf

[server]
# Greatly improves performance of some SQL queries.
optimizer_search_depth = 0

After applying the update, the site's status report has an error:

Mismatched entity and/or field definitions

The following changes were detected in the entity type and field definitions.

Webform submission

• The Webform submission entity type needs to be updated.

To fix it after having applied the (not quite correct) patch, run drush webform:repair

I will apply a fix to the MR soon. It's an easy fix.

I would like to note that in MySQL and MariaDB, setting optimizer_search_depth=0 also has a huge performance boost.

Could someone please review this? It's a significant performance increase, and it'd be nice to have this included for everyone.

Now looks good to me!

Tests do not pass. This probably needs a rebase.

Triggered a rebase