When flags are used as part of a views relationship the resulting query performance slows as more and more flag content is introduced. This issue arouse as part of how the message subscribe module uses flagging on content and provides a way to view all flagged content you have subscribed to.
The following is the query and explain output for before and after the attached patch.
EXPLAIN SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM node_field_data node_field_data INNER JOIN flagging flagging_node_field_data ON node_field_data.nid = flagging_node_field_data.flagged_entity__target_id_int AND flagging_node_field_data.flag_id = 'subscribe_node' LEFT JOIN flagging flagging_node_field_data_1 ON node_field_data.nid = flagging_node_field_data_1.flagged_entity__target_id_int AND (flagging_node_field_data_1.flag_id = 'email_node' AND flagging_node_field_data_1.uid = '254') WHERE (( (flagging_node_field_data.uid = '254' ) )) subquery;
+----+--------------------+----------------------------+------+----------------------------------------------------------------+--------------------------------+---------+-----------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref
+----+--------------------+----------------------------+------+----------------------------------------------------------------+--------------------------------+---------+-----------------------------------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL
| 2 | DERIVED | flagging_node_field_data | ref | flagging_field__uid__target_id | flagging_field__uid__target_id | 4 | const
| 2 | DERIVED | node_field_data | ref | PRIMARY,node__id__default_langcode__langcode,node__status_type | PRIMARY | 4 | community_dev.flagging_node_field_data.f
| 2 | DERIVED | flagging_node_field_data_1 | ref | flagging_field__uid__target_id | flagging_field__uid__target_id | 4 | const
| 3 | DEPENDENT SUBQUERY | na | ref | PRIMARY | PRIMARY | 4 | community_dev.node_field_data.nid
+----+--------------------+----------------------------+------+----------------------------------------------------------------+--------------------------------+---------+-----------------------------------------
(END)
although it is defaulting to an index query times still range from 45-50 seconds for 4800 return values.
After adding an index on uid, flag_id, flagged_entity__target_id_int the average query time is about _.085 seconds_
+----+--------------------+----------------------------+------+-----------------------------------------------------------------------------------+----------------------------------------------------+---------+--
| id | select_type | table | type | possible_keys | key | key_len | r
+----+--------------------+----------------------------+------+-----------------------------------------------------------------------------------+----------------------------------------------------+---------+--
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | N
| 2 | DERIVED | flagging_node_field_data | ref | flagging_field__uid__target_id,flagging_flag_id_uid_flagged_entity__target_id_int | flagging_flag_id_uid_flagged_entity__target_id_int | 770 | c
| 2 | DERIVED | node_field_data | ref | PRIMARY,node__id__default_langcode__langcode,node__status_type | PRIMARY | 4 | c
| 2 | DERIVED | flagging_node_field_data_1 | ref | flagging_field__uid__target_id,flagging_flag_id_uid_flagged_entity__target_id_int | flagging_flag_id_uid_flagged_entity__target_id_int | 775 | c
| 3 | DEPENDENT SUBQUERY | na | ref | PRIMARY | PRIMARY | 4 | c
+----+--------------------+----------------------------+------+-----------------------------------------------------------------------------------+----------------------------------------------------+---------+--
The performance for the selection of data mimics this performance gain with query times around .11-.13 seconds.
Needs work
4.0
Flag core
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.
This is night and day with my Views with flag attached. Is this production ready?
Without the patch there is an error using PostgreSQL DB:
SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: bigint = character varying LINE 4: INNER JOIN "flagging" "flagging_taxonomy_term_field_data" ON taxonomy_term_field_data.tid = flagging_tax>
Module: 8.x-4.0-beta5
Drupal: 10.3.2
PostgreSQL: 16.3
Patch from #41 solved the error.