Optimize this query

Posted on

Question :

SELECT 
  `permission_tbl`.`service_id` AS `service_id`,
  `permission_tbl`.`shop_id` AS `shop_id`,
  `permission_tbl`.`easy_id` AS `easy_id`,
  `permission_tbl`.`permission_type` AS `permission_type`,
  `permission_tbl`.`send_sts` AS `send_sts`,
  `permission_tbl`.`subscription_id` AS `subscription_id`,
  `permission_tbl`.`subscription_date` AS `subscription_date`,
  `permission_tbl`.`record_log` AS `record_log`,
  `permission_tbl`.`record_create_time` AS `record_create_time`,
  `permission_tbl`.`record_update_time` AS `record_update_time` 
FROM
  `permission_tbl` 
UNION
SELECT 
  `permission_tbl`.`service_id` AS `service_id`,
  `dummy_shop_tbl`.`dummy_shop_id` AS `shop_id`,
  `permission_tbl`.`easy_id` AS `easy_id`,
  `permission_tbl`.`permission_type` AS `permission_type`,
  `permission_tbl`.`send_sts` AS `send_sts`,
  `permission_tbl`.`subscription_id` AS `subscription_id`,
  `permission_tbl`.`subscription_date` AS `subscription_date`,
  `permission_tbl`.`record_log` AS `record_log`,
  `permission_tbl`.`record_create_time` AS `record_create_time`,
  `permission_tbl`.`record_update_time` AS `record_update_time` 
FROM
  (
    `permission_tbl` 
    JOIN `dummy_shop_tbl`
  ) 
WHERE (
    (
      `permission_tbl`.`shop_id` = `dummy_shop_tbl`.`shop_id`
    ) 
    AND (
      `permission_tbl`.`service_id` = `dummy_shop_tbl`.`service_id`
    )
)

And Table Structure for permission_tbl and dummy_shop_tbl is as follows:

CREATE TABLE `permission_tbl` (
  `service_id` varchar(10) NOT NULL DEFAULT '',
  `shop_id` varchar(20) NOT NULL DEFAULT '',
  `easy_id` int(10) unsigned NOT NULL DEFAULT '0',
  `permission_type` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `send_sts` smallint(3) unsigned NOT NULL,
  `subscription_id` smallint(5) unsigned NOT NULL,
  `subscription_date` datetime NOT NULL,
  `record_log` tinyint(4) NOT NULL,
  `record_create_time` datetime DEFAULT NULL,
  `record_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`service_id`,`shop_id`,`easy_id`),
  KEY `idx_permission_tbl_easy_id` (`easy_id`),
  KEY `idx_permission_tbl_permission_type` (`permission_type`),
  KEY `idx_permission_tbl_send_sts` (`send_sts`),
  KEY `idx_permission_tbl_record_create_time` (`record_create_time`),
  KEY `idx_permission_tbl_record_update_time` (`record_update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


CREATE TABLE `dummy_shop_tbl` (
  `service_id` varchar(10) NOT NULL DEFAULT '',
  `dummy_shop_id` varchar(20) NOT NULL DEFAULT '',
  `shop_id` varchar(20) NOT NULL DEFAULT '',
  `record_log` tinyint(4) NOT NULL DEFAULT '1',
  `record_create_time` datetime NOT NULL,
  `record_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`service_id`,`dummy_shop_id`),
  KEY `idx_dummy_shop_tbl_shop_id` (`shop_id`),
  KEY `idx_dummy_shop_tbl_record_create_time` (`record_create_time`),
  KEY `idx_dummy_shop_tbl_record_update_time` (`record_update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer :

SELECT 
  `permission_tbl`.`service_id` AS `service_id`,
  IFNULL(`dummy_shop_tbl`.`dummy_shop_id`,`permission_tbl`.`shop_id`) AS `shop_id`,
  `permission_tbl`.`easy_id` AS `easy_id`,
  `permission_tbl`.`permission_type` AS `permission_type`,
  `permission_tbl`.`send_sts` AS `send_sts`,
  `permission_tbl`.`subscription_id` AS `subscription_id`,
  `permission_tbl`.`subscription_date` AS `subscription_date`,
  `permission_tbl`.`record_log` AS `record_log`,
  `permission_tbl`.`record_create_time` AS `record_create_time`,
  `permission_tbl`.`record_update_time` AS `record_update_time` 
FROM `permission_tbl` 
LEFT JOIN `dummy_shop_tbl` ON `permission_tbl`.`shop_id` = `dummy_shop_tbl`.`shop_id`
    AND `permission_tbl`.`service_id` = `dummy_shop_tbl`.`service_id`

Leave a Reply

Your email address will not be published. Required fields are marked *