Return to Question

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.

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.

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

Please find some of the relevant database variable settings.