需要优化左连接下的这个视图查询

vlf7wbxs 于 2021-06-19 发布在 Mysql

关注(0)|答案(1)|浏览(273)

DROP VIEW IF EXISTS `view_pendinginvoices`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_pendinginvoices` AS 
SELECT
  `p`.`mrno`             AS `MRNO`,
  CONCAT(`p`.`patientname`) AS `PATIENTNAME`,
  `v`.`patient_visit_id` AS `visitno`,
  SUM(`pb`.`amount`)     AS `amount`,
  `p`.`patient_id`       AS `patient_id`,
  `v`.`visit_date`       AS `visit_date`,
  `v`.`visittype`        AS `visittype`,
  `v`.`site_id`          AS `site_id`,
  `v`.`visitid`          AS `visitid`
FROM (((`patient` `p`
LEFT JOIN `prefix_master` `pm`
   ON ((`pm`.`id` = `p`.`patprefix`)))
JOIN `view_pendinginvoices_sub` `pb`
   ON ((`p`.`patient_id` = `pb`.`PATIENTID`)))
JOIN `visit` `v`
   ON ((`v`.`visitid` = `pb`.`VISIT_ID`)))
GROUP BY `p`.`mrno`,`pm`.`prefix`,`p`.`patientname`,`v`.`patient_visit_id`,`p`.`patient_id`,`v`.`visit_date`,`v`.`visittype`,`v`.`site_id`,`v`.`visitid`
;$$
DELIMITER ;

mysql

来源：https://stackoverflow.com/questions/52941657/need-to-optimize-this-view-query-under-the-left-join

1条答案

按热度按时间

vql8enpb1#

看看你的代码，似乎有很多（）都是无用的，而且只有一个值

DROP VIEW IF EXISTS `view_pendinginvoices`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER 
    VIEW `view_pendinginvoices` AS 
SELECT
  `p`.`mrno`             AS `MRNO`,
  `p`.`patientname`      AS `PATIENTNAME`,
  `v`.`patient_visit_id` AS `visitno`,
  SUM(`pb`.`amount`)     AS `amount`,
  `p`.`patient_id`       AS `patient_id`,
  `v`.`visit_date`       AS `visit_date`,
  `v`.`visittype`        AS `visittype`,
  `v`.`site_id`          AS `site_id`,
  `v`.`visitid`          AS `visitid`
FROM `patient` `p`
    LEFT JOIN `prefix_master` `pm` ON `pm`.`id` = `p`.`patprefix`
    JOIN `view_pendinginvoices_sub` `pb` ON `p`.`patient_id` = `pb`.`PATIENTID`
    JOIN `visit` `v` ON `v`.`visitid` = `pb`.`VISIT_ID`
GROUP BY `p`.`mrno`
        ,`pm`.`prefix`
        ,`p`.`patientname`
        ,`v`.`patient_visit_id`
        ,`p`.`patient_id`
        ,`v`.`visit_date`
        ,`v`.`visittype`
        ,`v`.`site_id`
        ,`v`.`visitid`
;$$

DELIMITER ;

如果您需要提高性能，请确保在join中涉及的列上有适当的索引

create index  idx1  on patient (patient_id, patprefix )

赞(0）回复(0）举报 2021-06-20

我来回答

需要优化左连接下的这个视图查询

1条答案

相关问题

热门标签

最新问答