paragraphs_item_field_data table indexes

I recently encountered an issue where the lack of an index on paragraphs_item_field_data was causing major performance issues on a site. The problem comes up when there's a View that shows Paragraphs, and there's a contextual filter on the Parent ID.

I wasn't sure whether to post about it here because this may just be due to my particular use case, but it seems like this could be a fairly common occurrence - a node may have a field that points to Paragraphs, and a view that shows them based on the node ID. However I've set this to a Feature Request rather than a Bug Report for that reason. In my case I was also using a viewfield on the node, so this slow view was being displayed every time a node was loaded causing major performance degradation.

The view has a filter on the Paragraph type and status, the default filters when creating a view for a Paragraph, and a Contextual Filter on Parent ID. I was able to reproduce it on a fresh Drupal install by creating a Paragraph view and just adding the Contextual Filter.

That generates this query:

SELECT paragraphs_item_field_data.id AS id, '[the_view_name]:block_1' AS view_name
FROM
paragraphs_item_field_data paragraphs_item_field_data
WHERE (paragraphs_item_field_data.parent_id = '[the_node_id]') AND ((paragraphs_item_field_data.type IN ('[the_paragraph_type]')) AND (paragraphs_item_field_data.status = '1'))
LIMIT 10 OFFSET 0

Since the existing index (paragraphs__parent_fields) starts with parent_type, it wasn't being used here. I added an index on (parent_id, type, status, id), which solved the problem in my case. I suppose it might also be possible to just include the parent_type in the view and make it use the existing index, but I didn't realize there was any need to do that.

It also takes a pretty large data set to start to cause problems. My paragraphs_item_field_data table is about 700k rows currently.

So like I said above, I'm not entirely sure what the best course would be here or even if it's something that needs to be changed, but it caused such a major performance hit that I felt like it was worth reporting.