MySQL Forums
Forum List  »  NDB clusters

Struggling to Optimize The Query
Posted by: ch srinivas
Date: July 27, 2016 09:05AM

Please can any one help to resolve the optimizing Query below:

Query Execution Plan:

mysql> explain select `sm`.`goid` AS `metric_id`,`sm`.`uuid` AS `uuid`,`sm`.`published_service_goid` AS `api_id`,`p`.`name` AS `api_name`,`p`.`routing_uri` AS `api_uri`,`sm`.`resolution` AS `resolution`,`sm`.`period_start` AS `bin_start_time`,`sm`.`end_time` AS `bin_end_time`,`smd`.`front_min` AS `front_min`,`smd`.`front_max` AS `front_max`,`smd`.`front_sum` AS `front_sum`,`smd`.`back_min` AS `back_min`,`smd`.`back_max` AS `back_max`,`smd`.`back_sum` AS `back_sum`,`smd`.`attempted` AS `hits_total`,`smd`.`completed` AS `hits_success`,(`smd`.`attempted` - `smd`.`completed`) AS `hits_total_errors`,(`smd`.`attempted` - `smd`.`authorized`) AS `hits_policy_errors`,(`smd`.`authorized` - `smd`.`completed`) AS `hits_routing_errors`,(case when (`mcmk`.`mapping1_key` = 'API_KEY') then `mcmv`.`mapping1_value` when (`mcmk`.`mapping2_key` = 'API_KEY') then `mcmv`.`mapping2_value` when (`mcmk`.`mapping3_key` = 'API_KEY') then `mcmv`.`mapping3_value` when (`mcmk`.`mapping4_key` = 'API_KEY') then `mcmv`.`mapping4_value` when (`mcmk`.`mapping5_key` = 'API_KEY') then `mcmv`.`mapping5_value` end) AS `api_key`,(case when (`mcmk`.`mapping1_key` = 'API_METHOD') then `mcmv`.`mapping1_value` when (`mcmk`.`mapping2_key` = 'API_METHOD') then `mcmv`.`mapping2_value` when (`mcmk`.`mapping3_key` = 'API_METHOD') then `mcmv`.`mapping3_value` when (`mcmk`.`mapping4_key` = 'API_METHOD') then `mcmv`.`mapping4_value` when (`mcmk`.`mapping5_key` = 'API_METHOD') then `mcmv`.`mapping5_value` end) AS `api_method` from ((((`published_service` `p` join `service_metrics` `sm`) join `service_metrics_details` `smd`) join `message_context_mapping_values` `mcmv`) join `message_context_mapping_keys` `mcmk`) where ((`sm`.`published_service_goid` = `p`.`goid`) and (`sm`.`goid` = `smd`.`service_metrics_goid`) and (`smd`.`mapping_values_goid` = `mcmv`.`goid`) and (`mcmv`.`mapping_keys_goid` = `mcmk`.`goid`) and ((`mcmk`.`mapping1_key` = 'API_KEY') or (`mcmk`.`mapping2_key` = 'API_KEY') or (`mcmk`.`mapping3_key` = 'API_KEY') or (`mcmk`.`mapping4_key` = 'API_KEY') or (`mcmk`.`mapping5_key` = 'API_KEY')) and ((`mcmk`.`mapping1_key` = 'API_METHOD') or (`mcmk`.`mapping2_key` = 'API_METHOD') or (`mcmk`.`mapping3_key` = 'API_METHOD') or (`mcmk`.`mapping4_key` = 'API_METHOD') or (`mcmk`.`mapping5_key` = 'API_METHOD')));
+----+-------------+-------+--------+-----------------------------+---------------------+---------+-----------------------------------+-------+--------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------+---------------------+---------+-----------------------------------+-------+--------------------------------------------------------------+
| 1 | SIMPLE | mcmk | ALL | PRIMARY,mcmk_mt | NULL | NULL | NULL | 2 | Parent of 4 pushed join@1; Using where with pushed condition |
| 1 | SIMPLE | mcmv | ref | PRIMARY,mapping_keys_goid | mapping_keys_goid | 16 | lrsdata.mcmk.goid | 23066 | Child of 'mcmk' in pushed join@1 |
| 1 | SIMPLE | smd | ref | PRIMARY,mapping_values_goid | mapping_values_goid | 16 | lrsdata.mcmv.goid | 34 | Child of 'mcmv' in pushed join@1 |
| 1 | SIMPLE | sm | eq_ref | PRIMARY,i_sm_servicegoid | PRIMARY | 16 | lrsdata.smd.service_metrics_goid | 1 | Child of 'smd' in pushed join@1 |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 18 | lrsdata.sm.published_service_goid | 1 | Using where |
+----+-------------+-------+--------+-----------------------------+---------------------+---------+-----------------------------------+-------+--------------------------------------------------------------+
5 rows in set (0.00 sec)


Involved Tables list in Query:

mysql> show create table published_service\G
*************************** 1. row ***************************
Table: published_service
Create Table: CREATE TABLE `published_service` (
`goid` varbinary(16) NOT NULL,
`version` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`policy_xml` mediumtext,
`policy_goid` varbinary(16) DEFAULT NULL,
`wsdl_url` varchar(255) DEFAULT NULL,
`wsdl_xml` mediumtext,
`disabled` tinyint(1) NOT NULL DEFAULT '0',
`soap` tinyint(1) NOT NULL DEFAULT '1',
`internal` tinyint(1) NOT NULL DEFAULT '0',
`routing_uri` varchar(128) DEFAULT NULL,
`default_routing_url` mediumtext,
`http_methods` mediumtext,
`lax_resolution` tinyint(1) NOT NULL DEFAULT '0',
`wss_processing` tinyint(1) NOT NULL DEFAULT '1',
`tracing` tinyint(1) NOT NULL DEFAULT '0',
`folder_goid` varbinary(16) DEFAULT NULL,
`soap_version` varchar(20) DEFAULT 'UNKNOWN',
`uuid` varchar(48) DEFAULT NULL,
PRIMARY KEY (`goid`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> show create table service_metrics\G
*************************** 1. row ***************************
Table: service_metrics
Create Table: CREATE TABLE `service_metrics` (
`goid` binary(16) NOT NULL,
`nodeid` varchar(255) NOT NULL,
`published_service_goid` binary(16) NOT NULL,
`resolution` int(11) NOT NULL,
`period_start` bigint(20) NOT NULL,
`start_time` bigint(20) NOT NULL,
`interval_size` int(11) NOT NULL,
`end_time` bigint(20) NOT NULL,
`attempted` int(11) NOT NULL,
`authorized` int(11) NOT NULL,
`completed` int(11) NOT NULL,
`back_min` int(11) DEFAULT NULL,
`back_max` int(11) DEFAULT NULL,
`back_sum` bigint(19) NOT NULL,
`front_min` int(11) DEFAULT NULL,
`front_max` int(11) DEFAULT NULL,
`front_sum` bigint(19) NOT NULL,
`service_state` varchar(16) DEFAULT NULL,
`uuid` varchar(48) DEFAULT NULL,
`cluster_hostname` varchar(48) DEFAULT NULL,
PRIMARY KEY (`goid`),
UNIQUE KEY `nodeid` (`nodeid`,`published_service_goid`,`resolution`,`period_start`),
KEY `i_sm_nodeid` (`nodeid`),
KEY `i_sm_servicegoid` (`published_service_goid`),
KEY `i_sm_pstart` (`period_start`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table service_metrics_details\G
*************************** 1. row ***************************
Table: service_metrics_details
Create Table: CREATE TABLE `service_metrics_details` (
`service_metrics_goid` binary(16) NOT NULL,
`mapping_values_goid` binary(16) NOT NULL,
`attempted` int(11) NOT NULL,
`authorized` int(11) NOT NULL,
`completed` int(11) NOT NULL,
`back_min` int(11) DEFAULT NULL,
`back_max` int(11) DEFAULT NULL,
`back_sum` bigint(19) NOT NULL,
`front_min` int(11) DEFAULT NULL,
`front_max` int(11) DEFAULT NULL,
`front_sum` bigint(19) NOT NULL,
PRIMARY KEY (`service_metrics_goid`,`mapping_values_goid`),
KEY `mapping_values_goid` (`mapping_values_goid`),
CONSTRAINT `service_metrics_details_ibfk_1` FOREIGN KEY(`mapping_values_goid`) REFERENCES `message_context_mapping_values` (`goid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `service_metrics_goid` FOREIGN KEY(`service_metrics_goid`) REFERENCES `service_metrics` (`goid`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table message_context_mapping_values\G
*************************** 1. row ***************************
Table: message_context_mapping_values
Create Table: CREATE TABLE `message_context_mapping_values` (
`goid` binary(16) NOT NULL,
`digested` char(36) COLLATE utf8_bin NOT NULL,
`mapping_keys_goid` binary(16) NOT NULL,
`auth_user_provider_id` binary(16) DEFAULT NULL,
`auth_user_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`auth_user_unique_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`service_operation` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`mapping1_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`mapping2_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`mapping3_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`mapping4_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`mapping5_value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`create_time` bigint(20) DEFAULT NULL,
PRIMARY KEY (`goid`),
KEY `mapping_keys_goid` (`mapping_keys_goid`),
KEY `digested` (`digested`),
CONSTRAINT `message_context_mapping_values_ibfk_2` FOREIGN KEY(`mapping_keys_goid`) REFERENCES `message_context_mapping_keys` (`goid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql> show create table message_context_mapping_keys\G
*************************** 1. row ***************************
Table: message_context_mapping_keys
Create Table: CREATE TABLE `message_context_mapping_keys` (
`goid` binary(16) NOT NULL,
`version` int(11) NOT NULL,
`digested` char(36) NOT NULL,
`mapping1_type` varchar(36) DEFAULT NULL,
`mapping1_key` varchar(128) DEFAULT NULL,
`mapping2_type` varchar(36) DEFAULT NULL,
`mapping2_key` varchar(128) DEFAULT NULL,
`mapping3_type` varchar(36) DEFAULT NULL,
`mapping3_key` varchar(128) DEFAULT NULL,
`mapping4_type` varchar(36) DEFAULT NULL,
`mapping4_key` varchar(128) DEFAULT NULL,
`mapping5_type` varchar(36) DEFAULT NULL,
`mapping5_key` varchar(128) DEFAULT NULL,
`create_time` bigint(20) DEFAULT NULL,
PRIMARY KEY (`goid`),
KEY `digested` (`digested`),
KEY `mcmk_mt` (`mapping1_key`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Struggling to Optimize The Query
1628
July 27, 2016 09:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.