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.
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.