MySQL optimizes query for fast fetch

Question

I have something like this:

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

My problem is that both tables (PATIENTS AND CASES) have millions of rows, and year () disables my indexes and makes my query very slow.

How can I optimize it to work faster? (I tried using a nested SELECT to retrieve only the dates I want, but returns nothing).

Thank you in advance!

EDIT: Since my question may look a bit vague, plz help me optimize the following code because it gets stuck when fetching.

    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

All columns have indexes, and each of them has several million rows.

My goal is to show the average income of patients born in a particular year, and contacted us from 2005 to 2010.

, , ;)

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