CASE1.

변경전

select sql_no_cache from Posts as p
join (
select from PostMember
where 1 = 1 and MemberId in ( 33,34,35,36,40,44,45,46,47,48,49,53,54)
group by PostId
) as pm on pm.PostId = p.id
join Folders as l on l.PostId = p.id
where 1 = 1 and l.UserId in (162777)

변경후

select sql_no_cache /! straight_join / *
from Folders as l
join (
select PostId, MemberId from PostMember
where MemberId in ( 33,34,35,36,40,44,45,46,47,48,49,53,54)
) as pm on l.PostId = pm.PostId
join Posts as p on l.PostId = p.id
where 1 = 1 and l.UserId in (162777)

Point

1. 인덱스 생성: Folders 테이블의 UserID, PostId 컬럼으로 생성
   create index IDX_Folders_UserId_PostId ON Folders(UserId, PostId);

추가 튜닝 고려 사항

1. sselect list * 에서 사용할 컬럼으로 작성 -> 인덱스 작성등 영향
2. 데이터 증가에 따른 쿼리 속도 저하 -> 날짜 제한 등 데이터 표시 제한을 설정 할 수 있는지 등

---

CASE2.

변경전

select sql_no_cache count(1) as cnt, p.id as PostId, p.img, p.UserId, u.nick, u.profileImg, p.thumb_width, p.thumb_height, p.CONVS_STUS,
u.UserId as vpId, p.format
from Posts as p
join Folders as l on l.PostId = p.id
join (
select * from PostMember
where MemberId in (33,34,35,36,40,44,45,46,47,48,49,53,54)
group by PostId
) as pm on pm.PostId = p.id
join Users as u on u.id = p.UserId
where l.createdAt >= '2022-11-06 15:00:00' and l.createdAt <= '2022-11-13 15:00:00'
group by p.id

변경후

select sql_no_cache /! straight_join / count(1) as cnt, p.id as PostId, p.img, u.UserId, u.nick, u.profileImg, p.thumb_width, p.thumb_height, p.CONVS_STUS,
u.UserId as vpId, p.format
from (
select PostId from PostMember
where MemberId in (33,34,35,36,40,44,45,46,47,48,49,53,54)
group by PostId order by null
) as pm
join Folders as l on pm.PostId = l.PostId
join Posts as p on l.PostId = p.id
join Users as u on p.UserId = u.id
where l.createdAt >= '2022-11-06 15:00:00' and l.createdAt <= '2022-11-13 15:00:00'
group by l.PostId
order by null

Point

1. Group by 시 정렬 작업 삭제
2. 복합 인덱스 생성
3. 조인 순서 변경
4. From 절에 나온 순서대로 조인 하도록 제어(힌트)

---

CASE3.

변경전

select p.id, img, format, p.content, u.id as userId, u.id as UserId, p.thumb_width, p.thumb_height, p.createdAt ,p.CONVS_STUS
from Posts as p
join Users as u on u.id = p.UserId and u.type = 1 and deletedAt is null
join PostMember as pm on pm.PostId = p.id
and pm.MemberId in (125, 124, 123, 122, 121, 120, 119, 118) where 1 = 1 and u.status = 2
group by p.id order by p.id desc limit 0, 40;

변경후

select sql_no_cache p.id, img, format, p.content, u.id as userId, u.id as UserIdd, p.thumb_width, p.thumb_height, p.createdAt , p.CONVS_STUS
from (select UserId, id, id2, img, format, content, thumb_width, thumb_height, createdAt, CONVS_STUS from (select pp1.id from Posts as pp1 join PostMember as pm on pm.PostId = pp1.id where MemberId in (223,224,225,226,228,230,231,232,249) group by pp1.id order by pp1.id desc limit 10000, 40 ) as p1
join (select UserId, id as id2, img, format, content, thumb_width, thumb_height, createdAt, CONVS_STUS from Posts) as p2 on p1.id = p2.id2) as p
join Users as u on u.id = p.UserId and u.type = 1 and u.status = 2

Point

1. 인라인뷰 사용, 인라인뷰에서 조인으로 또는 조인에서 인라인뷰로 변경
2. 조인 순서 변경

---

CASE4.

변경전

select sql_no_cache u.*, um.MemberId
from Users as u
join UserMember as um on um.UserId = u.id
where um.MemberId in (125, 124, 123, 122, 121, 120, 119, 118)
and u.market_push = 1
group by u.id, um.MemberId
order by null

변경후

select sql_no_cache id, disturb_s, disturb_e, disturb, fcmTocken, langType
from Users as u
left join (select UserId, MemberId from UserMember where MemberId in (125, 124, 123, 122, 121, 120, 119, 118)) as um
on u.id = um.UserId
where um.UserId is not null
and u.market_push = 1

Point

1. IN 조건을 EXISTS 또는 조인으로 변경 혹은 조인을 IN, EXISTS로 변경
2. 그룹함수 제거
3. 복합 인덱스 생성

---

CASE5.

변경전

select sql_no_cache f.*, u.rest from FollowingVps as f
join VpMember as vm on vm.UserId = f.followingId
join Member as m on m.id = f.MemberId
join Users as u on u.id = f.followingId
where 1 = 1 and f.userId in (336)
group by followingId, f.userId, m.id

변경후

select sql_no_cache f.*, u.rest
from FollowingVps as f
join Users as u on u.id = f.followingId
where 1 = 1 and f.userId in (336)
group by f.followingId, f.userId, f.memberId

---