Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: sql 优化问题
|
username: rw12306
Do you have any optimization suggestions for this SQL? It’s extremely slow right now.
EXPLAIN analyze
select
cf.MEDICAL_INSTITUT_CODE as orgId,
cf.ORG_NAME as orgName,
cf.PRESCRIPTION_NUMBER as pre_code,
-- Prescription number
cf.VISITING_SERIAL_NUMBER as serial_code,
-- Outpatient number
cf.PRESCRIPTION_PROPERTY as pre_type,
-- Prescription type
ifnull(cf.recipel_type, '1') as recipel_type,
-- Prescription category
cf.PRE_TIME as business_date,
cf.FILE_NUMBER as patient_id,
-- Patient ID
br.patient_name,
-- Patient name
br.GENDER_CODE as sex,
-- Gender code
br.ID_TYPE as card_type,
-- ID type
br.ID_NUMBER as card_code,
-- ID number
br.BIRTH_DATE as birth_date,
-- Birth date
cf.PRE_DOCTOR_NAME as doc_name,
-- Prescribing doctor
cf.VISIT_DEP_NAME as dep_name,
-- Prescribing department
cf.DEPLOY_PHARMACIST_NAME as deploy_doc_name,
-- Dispensing pharmacist
cf.CHECK_PHARMACIST_NAME as check_doc_name,
-- Verifying pharmacist
cf.ISSUE_PHARMACIST_NAME as dispensing_doc_name,
-- Issuing pharmacist
cf.PRE_CHECK_DOC_CODE as examine_doc_name,
-- Reviewing pharmacist
cf.tcm_take_means,
-- Traditional Chinese medicine taking method
cf.tcm_decoction_method,
-- Traditional Chinese medicine decoction method code
antibiotic,
infusion_1,
infusion_2,
infusion_3,
union_drug,
base_drug,
infusion,
pre_amount,
-- max(zd.diag_name) as diag_name, -- Diagnosis name
zd.diag_name as diag_name,
mx.drug_name
from
yiliao.TB_CIS_OP_PRESCRIPTION cf use INDEX(PRIMARY)
inner join
(
select
/*+ AGG_TO_COP() */
mx.PRESCRIPTION_NUMBER,
mx.MEDICAL_INSTITUT_CODE,
GROUP_CONCAT(distinct mx.ANTIBIOTIC_SIGN order by mx.ANTIBIOTIC_SIGN) as antibiotic,
-- Antibiotic sign
max(case when mx.ANTIBIOTIC_SIGN = '1' then 1 else 0 end) as infusion_1,
-- Non-restricted use antibiotics
max(case when mx.ANTIBIOTIC_SIGN = '2' then 1 else 0 end) as infusion_2,
-- Restricted use antibiotics
max(case when mx.ANTIBIOTIC_SIGN = '3' then 1 else 0 end) as infusion_3,
-- Special use antibiotics
count(case when mx.ANTIBIOTIC_SIGN != '0' then mx.id else null end) as union_drug,
-- Combined medication
GROUP_CONCAT(distinct ifnull(mx.DRUG_BASE_ATTR_CODE, '1') order by mx.DRUG_BASE_ATTR_CODE) as base_drug,
-- Drug base attribute
max(case when mx.DRUG_USE_MEANS_CODE = '404' then 1 else 0 end) as infusion,
-- Infusion prescription
sum(mx.DRUG_AMOUNT) as pre_amount,
-- Prescription amount
GROUP_CONCAT(distinct mx.ITEM_NAME) as drug_name
-- Drug name collection
from
yiliao.TB_CIS_OP_PRESCRIPTION_DETAIL mx
where
mx.PRE_TIME >= '2021-01-01 00:00:00'
and mx.PRE_TIME <'2021-01-07 00:00:00'
group by
mx.MEDICAL_INSTITUT_CODE,
mx.PRESCRIPTION_NUMBER
) mx
on
cf.PRESCRIPTION_NUMBER = mx.PRESCRIPTION_NUMBER
and cf.MEDICAL_INSTITUT_CODE = mx.MEDICAL_INSTITUT_CODE
inner join
(
select /*+ AGG_TO_COP() */
pat.FILE_NUMBER,
pat.patient_name,
-- Patient name
pat.GENDER_CODE ,
-- Gender code
pat.ID_TYPE ,
-- ID type
pat.ID_NUMBER ,
-- ID number
date(pat.BIRTH_DATE) as BIRTH_DATE
-- Birth date
from
yiliao.TB_CIS_PATIENT_INFO pat
##use index(PRIMARY)
where
FILE_NUMBER in (
select
FILE_NUMBER
from
yiliao.TB_CIS_OP_PRESCRIPTION_DETAIL mx
where
mx.PRE_TIME >= '2021-01-01 00:00:00'
and mx.PRE_TIME <'2021-01-07 00:00:00'
)
)
br on
cf.FILE_NUMBER = br.FILE_NUMBER
left join index_diagnosis zd USE index(index_diagnosis_serial_code_IDX) on
cf.MEDICAL_INSTITUT_CODE = zd.orgid
and cf.VISITING_SERIAL_NUMBER = zd.serial_code
|
username: 
|
username: 
|
username: 
