Timeout when removing a webform field

I would lean towards committing ✨ [Performance] Add an index on webform_submission_data name and property Needs review and then make this performance improvement.

@jrockowitz, would it make sense to merge both developments into a single MR? The ✨ [Performance] Add an index on webform_submission_data name and property Needs review issue introduces an index on the 'name' and 'property' values, while this MR adds an index on 'name' and 'webform_id.'.

It is very possible the creating the index proposed in ✨ [Performance] Add an index on webform_submission_data name and property Needs review will address this issue. My understanding is the name part of the name,property will be used by the delete query

I am not sure removing the SQL IN condition will be a significant improvement.
@see https://stackoverflow.com/questions/31500/do-indexes-work-with-in-clause

Hi @jrockowitz,

Thank you for your suggestion regarding the index on name,property. However, I believe that a composite index including both webform_id and name would provide better performance for the query in question. Here’s my reasoning:

Query Condition Analysis:
Based on the query you referenced, the WHERE clause includes both webform_id and name. This means that both columns are involved in filtering the results.
While an index on name,property may help in some scenarios, it does not fully cover the webform_id filter condition.
A composite index on webform_id and name allows the database engine to use both columns together for filtering. This reduces the need for additional scans and increases the speed of the query, especially in large datasets where webform_id is often a primary filter.
Without this composite index, the database would either:

• Perform a less efficient scan using a partial index (e.g., on name or property alone).
• Fall back to a full table scan if no suitable index exists.

Performance Testing Evidence:
To demonstrate the impact, I tested the query on a sample dataset. Below are the results:

Query without a composite index:

Query with a composite index on name and property:

Query with a composite index on webform_id and name:

These tests show a clear improvement in query performance when using the composite index on webform_id and name and no real impact - the performance got worse - when using a composite index on name and property.

Database Optimization Best Practices:
It’s a best practice to align indexes with the columns used in the WHERE clause to minimize row scans.
Adding the composite index on webform_id and name would allow the query planner to leverage it effectively and deliver faster results.

I also agree that the use of the IN clause in this case is unlikely to cause a significant impact on query performance. However, I’d like to point out that the post you referenced pertains to SQL Server, which uses a different query engine than MySQL. While some general indexing principles may overlap, it’s important to account for differences in how these databases optimize queries and use indexes. In MySQL, aligning indexes with the columns in the WHERE clause is a crucial factor in achieving optimal performance on large datasets.