When building a view based on content revisions which uses the moderation control state filter, it takes a long time for the query to finish executing. With large data sets (+1 000 000 rows) it takes more than a minute to execute the query. This is a huge performance issue which makes it impossible to use a moderation state filter in conjunction with showing content revisions.
You'll need a large data set to see the performance issue clearly
- Fresh install
- Enable content moderation
- Create a content revisions view
- Add a moderation control state filter
Create an index for moderation state in the content moderation state field revision table. Doing this would decrease the query execution time to a fraction. Testing by creating the index directly in the db made the execution time go from > 1 min to < 1 second.
1. Create patch which adds an index to moderation state field revision table
2. Add tests
Needs work
10.1 ✨
It affects performance. It is often combined with the Needs profiling tag.
Used to track the progress of issues reviewed by the Drupal Needs Review Queue Initiative.
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.
I'll get the ball rolling on this issue once again.
@smustgrave, just like you pointed out, it was a pretty miserable experience trying to go from 9.2.x to 10.1.x. So, I've started on a new (feature) branch with 10.1.x as base. I'll make an attempt to fix things based on larowlan's feedback.
Just closed 🐛 Slow query joining on content_moderation_state_field_revision Active as a duplicate.