[미션 6-1] 조회 성능 개선하기

요구사항

주어진 데이터셋을 활용하여 아래 조회 결과를 100ms 이하로 반환

• 1) Coding as a Hobby 와 같은 결과를 반환하세요.

• 2) 프로그래머별로 해당하는 병원 이름을 반환하세요. (covid.id, hospital.name)

• 3) 프로그래밍이 취미인 학생 혹은 주니어(0-2년)들이 다닌 병원 이름을 반환하고 user.id 기준으로 정렬하세요. (covid.id, hospital.name, user.Hobby, user.DevType, user.YearsCoding)

• 4) 서울대병원에 다닌 20대 India 환자들을 병원에 머문 기간별로 집계하세요. (covid.Stay)

• 5) 서울대병원에 다닌 30대 환자들을 운동 횟수별로 집계하세요. (user.Exercise)

미션실습

1) Coding as a Hobby 와 같은 결과를 반환하세요.

# 조치 : Hobby 인덱스 설정
select count(*) / (select count(*) from programmer) * 100 as percent
from programmer
group by Hobby;

group By 에 사용되는 컬럼에 인덱스를 걸어두면 결과적으로 정렬되어 있는 컬럼을 그룹별 탐색 하는 것이 되므로 속도가 빨라진다. (INDEX는 항상 키 컬럼 순으로 정렬된 상태를 유지한다)

2) 프로그래머별로 해당하는 병원 이름을 반환하세요. (covid.id, hospital.name)

# 조치 : hospital 테이블의 code, name 에 인덱스 설정
select c.id, h.name
from covid c
         join hospital h on c.Hospital_code = h.code;

hospital 테이블에 code, name 순으로 인덱스를 걸어주면 해당 쿼리에 대해서는 인덱스가 모든 정보를 이미 갖고 있으므로 테이블 엑세스 없이 데이터 출력이 가능하다 (coverd index)

3) 프로그래밍이 취미인 학생 혹은 주니어(0-2년)들이 다닌 병원 이름을 반환하고 user.id 기준으로 정렬하세요. (covid.id, hospital.name, user.Hobby, user.DevType, user.YearsCoding)

# 조치 : 각 테이블 id pk 설정, hospital 테이블 code, name 인덱스 설정, programmer 테이블 Hobby 인덱스 설정
select P.id, H.name
from (select c.id, h.name
      from covid c
               join hospital h on c.Hospital_code = h.code) as H
         join (
    select id
    from programmer p
    where (p.Hobby = 'Yes' and (Student like 'Yes%' or p.YearsCoding = '0-2 years'))) as P on H.id = P.id
order by P.id;

4) 서울대병원에 다닌 20대 India 환자들을 병원에 머문 기간별로 집계하세요. (covid.Stay)

# 조치 : 조건절에 사용하는 모든 컬럼 인덱스 설정
select c.Stay, count(c.Stay) as patient
from hospital h
         join covid c on h.code = c.Hospital_code
where c.Hospital_code = 9
  and c.id in (
    select m.id
    from member m
             join programmer p on m.id = p.id
    where (m.age between 20 and 29)
      and p.Country = 'India'
)
group by c.Stay;

5) 서울대병원에 다닌 30대 환자들을 운동 횟수별로 집계하세요. (user.Exercise)

select programmer.Exercise, count(programmer.Exercise)
from programmer
         join member on programmer.id = member.id
where (member.age between 30 and 39)
  and programmer.id in (
    select covid.id
    from covid
             join hospital on covid.Hospital_code = hospital.code
    where Hospital_code = 9
)
group by programmer.Exercise;