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.
DESCRIBE
is not as descriptive asSHOW CREATE TABLE
.