Mysql groupby 최신데이터 1row 값

 

EXPLAIN
SELECT R1.sender_id, R2.status
FROM
(
    SELECT sender_id, MAX(tseq) AS seq
    FROM 조회테이블 AS R
    WHERE R.create_at BETWEEN '2022-01-01 00:00:00' AND '2022-07-31 23:59:59'
    GROUP BY sender_id
    ORDER BY NULL
) AS R1
INNER JOIN 조회테이블 AS R2
ON R1.seq = R2.seq AND R2.status = '상태값'

보통 검색해보면 다른 정보로 JOIN을 시도하는데, 기본 키로 조회 시도.

 

회사의 해당 테이블의 컬럼 수가 160개정도로.. 해당 쿼리를 실행하려고 하면 데이터베이스가 사망하려고 한다.

 

EXPLAIN
SELECT sender_id, MAX(seq) AS seq
FROM
(
    SELECT sender_id, MAX(seq) AS seq
    FROM 조회테이블 AS R1
    WHERE R1.create_at >= '2022-01-01 00:00:00' AND  R1.create_at < '2022-03-31 23:59:59'
    GROUP BY sender_id

    UNION ALL

    SELECT sender_id, MAX(seq) AS seq
    FROM 조회테이블 AS R2
    WHERE R2.create_at >= '2022-04-01 00:00:00' AND  R2.create_at < '2022-07-31 23:59:59'
    GROUP BY sender_id
) AS T
GROUP BY sender_id
ORDER BY NULL

 

인덱스는 태웠지만 GROUP BY로 인한 filesort가 거슬린다.

 

EXPLAIN
SELECT T.sender_id
FROM
(
    SELECT sender_id, MAX(seq) AS seq
    FROM
    (
        SELECT *
        FROM
        (
            SELECT sender_id, MAX(seq) AS seq
            FROM 조회테이블 AS R1
            WHERE R1.create_at >= '2022-01-01 00:00:00' AND  R1.create_at < '2022-03-31 23:59:59'
            GROUP BY sender_id
            ORDER BY NULL
        ) AS R1

        UNION ALL

        SELECT *
        FROM
        (
            SELECT sender_id, MAX(seq) AS seq
            FROM 조회테이블 AS R2
            WHERE R2.create_at >= '2022-04-01 00:00:00' AND  R2.create_at < '2022-07-31 23:59:59'
            GROUP BY sender_id
            ORDER BY NULL
        ) AS R2
    ) AS T
    GROUP BY sender_id
    ORDER BY NULL
) AS T
INNER JOIN 조회테이블 AS R
ON T.seq = R.seq AND R.status = '상태값'

완성된 쿼리. derived가 거슬리긴 하지만.. sort보단 빠를거라고 생각한다.

이 회사도 DBA가 있었으면 좋겠다...

끝.