优化mySQL查询以快速获取数据

Question

优化mySQL查询以快速获取数据

4

我有类似这样的东西：

SELECT INCOME
FROM PATIENTS
JOIN CASES
ON PATIENT_ID = CASE_PATIENT_ID
WHERE YEAR(DATE_OF_BIRTH) = '1955' AND YEAR(CASE_DATE)>'2000'
GROUP BY INCOME

我的问题是，PATIENTS和CASES这两个表都有数百万行数据，year()函数会使我的索引失效，并且使我的查询非常缓慢。如何优化它以使其运行更快？（我尝试使用嵌套SELECT来提取我想要的日期，但没有返回任何结果）。谢谢您提前帮助！

编辑：由于我的问题可能看起来有点模糊，请帮我优化以下代码，因为它在获取数据时卡住了。

    select round(avg(INCOME_PER_MONTH),2) as Average_Income,
case
    when WEIGHT/(HEIGHT*HEIGHT)*10000 < 15 then "Very Severly Underweight"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 15 and 16 then "Severly Underweight"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 16.1 and 18.5 then "Underweight"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 18.6 and 25 then "Normal (healthy weight)"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 25.1 and 30 then "Overweight"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 30.1 and 35 then "Obese Class I"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 35.1 and 40 then "Obese Class II"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 > 40 then "Obese Class III"
end
as BMI,
WEIGHT/(HEIGHT*HEIGHT)
from PATIENTS
join CASES
on PATIENT_ID = PAT_ID and PATIENTS.PATIENT_ID = CASES.PAT_ID
where CASES.DATE_OF_CONT  between '2005-01-01' and '2010-12-31'
and PATIENTS.DATE_OF_BIRTH between '1995-01-01' and '1995-12-31'
group by
case
    when WEIGHT/(HEIGHT*HEIGHT)*10000 < 15 then "Very Severly Underweight"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 15 and 16 then "Severly Underweight"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 16.1 and 18.5 then "Underweight"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 18.6 and 25 then "Normal (healthy weight)"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 25.1 and 30 then "Overweight"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 30.1 and 35 then "Obese Class I"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 between 35.1 and 40 then "Obese Class II"
    when WEIGHT/(HEIGHT*HEIGHT)*10000 > 40 then "Obese Class III"
end

所有列都有索引，每个列中都有几百万行数据。

我的目标是展示在特定年份出生并在2005年至2010年期间联系我们的患者的平均收入。

希望这能成为一个合适的挑战；)

- HappyCane

“DATE_OF_BIRTH”和“CASE_DATE”列中存储的数据格式是什么？你能举个例子吗？ - Nate

2

一个范围查询会更快：date_of_birth BETWEEN '1955-01-01' AND '1955-12-31' AND case_date > '2000-12-31' - Strawberry

它们被存储为日期（YY-mm-dd）。我尽量避免直接比较，因为那样就必须比较3分钟和500万个日期。 - HappyCane

YY-mm-dd 在 MySQL 中不是日期格式，它们都以 VARCHAR 存储。请执行 SHOW CREATE TABLE PATIENTS 和 SHOW CREATE TABLE CASES 命令，并将输出添加到您的问题中。 - miken32

病人：DATE_OF_BIRTH日期默认为空，案例：CASE_DATE日期默认为空。我尝试使用year（DATE），因为它将有助于我的代码，该代码将接受4位数字作为出生年份。 - HappyCane

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Richard St-Cyr · Answer 1

虽然不够美观，但这应该可行：

SELECT INCOME
FROM PATIENTS
JOIN CASES
ON PATIENT_ID = CASE_PATIENT_ID
WHERE DATE_OF_BIRTH BETWEEN STR_TO_DATE(CONCAT('1955', '-01-01'), '%Y-%m-%d') AND STR_TO_DATE(CONCAT('1955', '-12-31'), '%Y-%m-%d') AND 
      CASE_DATE > STR_TO_DATE(CONCAT('2000', '-12-31'), '%Y-%m-%d')
GROUP BY INCOME