Struggling to Optimize The Query

[ch srinivas]

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)