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. 

<pre>
*************************** 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</pre>

Please find the flagging table describe:
<pre>
| 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.' |
</pre>

Show create for the table node__field_date_of_collection 
<pre>
| 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.'
</pre>

Show create for the table node__og_audience
<pre>
| 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.'
</pre>
</pre>

Please find some of the relevant database variable settings. 
<pre>
[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
</pre>

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.