Join with certain tables causes execution time to skyrocket

Database Application: Ver 8.0.30 for Linux on x86_64 (MySQL Community Server – GPL)

Server: Ubuntu 18.04.6

RAM: 32GB

CPUs: 8 core

The underlying application framework is Drupal and it uses a query builder UI (Views module) to generate queries for reports. Please find the non performant query below. Without the join to the flagging table the query executes under few seconds. I have improved the query based on suggestions by @Rick James and @mustaccio. The query time still exceeds 4 minutes when joined with flagging table.

EXPLAIN SELECT 1 AS `expression`
FROM
`node_field_data` `node_field_data`
LEFT JOIN `flagging` `flagging_node_field_data` ON node_field_data.nid = flagging_node_field_data.entity_id AND flagging_node_field_data.flag_id = 'verify_blood_group'
LEFT JOIN `node__field_date_of_collection` `node__field_date_of_collection` ON node_field_data.nid = node__field_date_of_collection.entity_id AND node__field_date_of_collection.deleted = '0'
LEFT JOIN `node__og_audience` `node__og_audience` ON node_field_data.nid = node__og_audience.entity_id AND (node__og_audience.deleted = '0' AND node__og_audience.langcode = node_field_data.langcode)
WHERE ((node__og_audience.og_audience_target_id IN('30', '229', '5026', '60887', '198081', '350754', '519498', '519499', '566913', '568976', '571016', '642633', '739096', '769874', '770003', '800588', '1051756', '1056092', '1101838', '1465616', '1730929', '2045068', '2269366', '3535017', '1836317', '3387310', '9900000'))) AND ((`node_field_data`.`status` = '1') AND (`node_field_data`.`type` IN ('donation_record')) AND (node__field_date_of_collection.field_date_of_collection_value BETWEEN '2022-08-27' AND ('2022-09-02' + INTERVAL 1 DAY)));

Please see the Query Explain below.

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: node__field_date_of_collection
   partitions: NULL
         type: range
possible_keys: PRIMARY,field_date_of_collection_value
          key: field_date_of_collection_value
      key_len: 82
          ref: NULL
         rows: 22808
     filtered: 10.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: node_field_data
   partitions: NULL
         type: ref
possible_keys: PRIMARY,node__id__default_langcode__langcode,node_field__type__target_id,node__status_type
          key: PRIMARY
      key_len: 4
          ref: ebloodbanking8.node__field_date_of_collection.entity_id
         rows: 1
     filtered: 5.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: node__og_audience
   partitions: NULL
         type: ref
possible_keys: PRIMARY,og_audience_target_id,og_audience_entityid_deleted_langcode_value
          key: PRIMARY
      key_len: 5
          ref: ebloodbanking8.node__field_date_of_collection.entity_id,const
         rows: 1
     filtered: 10.00
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: flagging_node_field_data
   partitions: NULL
         type: ref
possible_keys: flagging_fid_etid,flagging_fid_uid_etid
          key: flagging_fid_etid
      key_len: 34
          ref: const
         rows: 388428
     filtered: 100.00
        Extra: Using where; Using index

Please find the flagging table describe:

| flagging | CREATE TABLE `flagging` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `flag_id` varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
  `uuid` varchar(128) CHARACTER SET ascii NOT NULL,
  `entity_type` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `entity_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `global` tinyint DEFAULT NULL,
  `uid` int unsigned NOT NULL COMMENT 'The ID of the target entity.',
  `session_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `flagging_fid_etid` (`flag_id`,`entity_id`),
  KEY `flagging_fid_uid_etid` (`flag_id`,`uid`,`entity_id`),
  KEY `flagging_type_fid_etid` (`entity_type`,`flag_id`,`entity_id`),
  KEY `flagging_type_fid_uid_etid` (`entity_type`,`flag_id`,`uid`,`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2135664 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='The base table for flagging entities.' |

Show create for the table node__field_date_of_collection

| node__field_date_of_collection | CREATE TABLE `node__field_date_of_collection` (
  `bundle` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  `deleted` tinyint NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
  `entity_id` int unsigned NOT NULL COMMENT 'The entity id this data is attached to',
  `revision_id` int unsigned NOT NULL COMMENT 'The entity revision id this data is attached to',
  `langcode` varchar(32) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The language code for this data item.',
  `delta` int unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  `field_date_of_collection_value` varchar(20) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'The date value.',
  PRIMARY KEY (`entity_id`,`deleted`,`delta`,`langcode`),
  KEY `bundle` (`bundle`),
  KEY `revision_id` (`revision_id`),
  KEY `field_date_of_collection_value` (`field_date_of_collection_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Data storage for node field field_date_of_collection.'

Show create for the table node__og_audience

| node__og_audience | CREATE TABLE `node__og_audience` (
  `bundle` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  `deleted` tinyint NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
  `entity_id` int unsigned NOT NULL COMMENT 'The entity id this data is attached to',
  `revision_id` int unsigned NOT NULL COMMENT 'The entity revision id this data is attached to',
  `langcode` varchar(32) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The language code for this data item.',
  `delta` int unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  `og_audience_target_id` int unsigned NOT NULL COMMENT 'The ID of the target entity.',
  PRIMARY KEY (`entity_id`,`deleted`,`delta`,`langcode`),
  KEY `bundle` (`bundle`),
  KEY `revision_id` (`revision_id`),
  KEY `og_audience_target_id` (`og_audience_target_id`),
  KEY `og_audience_entityid_deleted_langcode_value` (`entity_id`,`deleted`,`langcode`,`og_audience_target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='Data storage for node field og_audience.'

Show create for the table node_field_data

| node_field_data | CREATE TABLE `node_field_data` (
  `nid` int unsigned NOT NULL,
  `vid` int unsigned NOT NULL,
  `type` varchar(32) CHARACTER SET ascii NOT NULL COMMENT 'The ID of the target entity.',
  `langcode` varchar(12) CHARACTER SET ascii NOT NULL,
  `status` tinyint NOT NULL,
  `uid` int unsigned NOT NULL COMMENT 'The ID of the target entity.',
  `title` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `created` int NOT NULL,
  `changed` int NOT NULL,
  `promote` tinyint NOT NULL,
  `sticky` tinyint NOT NULL,
  `default_langcode` tinyint NOT NULL,
  `revision_translation_affected` tinyint DEFAULT NULL,
  PRIMARY KEY (`nid`,`langcode`),
  KEY `node__id__default_langcode__langcode` (`nid`,`default_langcode`,`langcode`),
  KEY `node__vid` (`vid`),
  KEY `node_field__type__target_id` (`type`),
  KEY `node_field__uid__target_id` (`uid`),
  KEY `node_field__created` (`created`),
  KEY `node_field__changed` (`changed`),
  KEY `node__status_type` (`status`,`type`,`nid`),
  KEY `node__frontpage` (`promote`,`status`,`sticky`,`created`),
  KEY `node__title_type` (`title`(191),`type`(4))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='The data table for node entities.'

Please find some of the relevant database variable settings.

[mysqld]
default-storage-engine=InnoDB
join_buffer_size = 8M
read_buffer_size = 4M
sort_buffer_size = 8M
thread_cache_size = 8
interactive_timeout = 60
wait_timeout = 60 # Time in seconds
connect_timeout = 10
max_connect_errors = 10000
tmp_table_size = 32M
max_heap_table_size = 32M

# InnoDB Settings
innodb_buffer_pool_size=18G
innodb_flush_log_at_trx_commit=2
#Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=6G
innodb_log_buffer_size=64M
innodb_flush_method=O_DIRECT
innodb_buffer_pool_instances=8
innodb_stats_on_metadata=0
innodb_lock_wait_timeout=100
innodb_write_io_threads=8

Please share what changes can be made to make this more performant.Indexes have been added to flagging table. Please share monitoring tools that can help us understand the problems better, database global variable changes that can make this query execution faster. Thanks.

Note: As suggested by Rick James, changing the data type for column entity_id in flagging table from varchar to unsigned int resolved the query performance during joins. Thanks.