문제 1 헤비유저가 소유한 장소

1. GROUP BY + IN

SELECT *
FROM places
WHERE host_id in(
    SELECT HOST_ID FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(ID) >= 2
)
ORDER BY id

성능 중간
메인쿼리는 인덱스 스캔 , filesort가 발생하지 않음.
서브 쿼리는 풀 스캔, filesort가 발생한다.

2. COUNT + PARTITION BY

SELECT PL.ID, PL.NAME, PL.HOST_ID
FROM 
    (
        SELECT * , COUNT(*) OVER (PARTITION BY HOST_ID) AS HOST_COUNT
        FROM PLACES
    ) PL
WHERE PL.HOST_COUNT > 1
ORDER BY PL.ID;

메인쿼리, 서브 쿼리 둘 다 인덱스를 타지 않고 풀 스캔,
둘 다 filesort가 발생한다.
성능 최저

3. GROUP BY + EXIST

SELECT *
FROM PLACES PL1 
WHERE EXISTS (
                SELECT 1
                FROM PLACES PL2
                WHERE PL1.HOST_ID = PL2.HOST_ID            
                GROUP BY HOST_ID
                HAVING COUNT(*) > 1
             )
ORDER BY ID;

EXIST(서브쿼리)는 존재여부만 체크한다.
존재여부가 확인되면 바로 종료되기 때문에
성능 최상

SELECT 1은 해당 절에서 컬럼이 불필요하기 때문에 의미없는 1 추가

문제 2 우유와 요거트가 담긴 장바구니

1. 서브쿼리

SELECT cart_id
FROM cart_products
WHERE name = 'Milk' and cart_id IN (
    SELECT cart_id
    FROM cart_products
    WHERE name = 'Yogurt'
)
ORDER BY cart_id

2.LEFT JOIN

SELECT cp1.cart_id
FROM cart_products as cp1
LEFT JOIN cart_products as cp2
ON cp1.cart_id = cp2.cart_id
WHERE cp1.name = 'Milk' and cp2.name = 'Yogurt'
ORDER BY cp1.cart_id

3. INNER JOIN

SELECT DISTINCT A.CART_ID
FROM 
 (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Yogurt') as A
INNER JOIN
 (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Milk') as B
ON A.CART_ID = B.CART_ID
ORDER BY CART_ID

4. JOIN

SELECT A.CART_ID
FROM 
 (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Yogurt') as A, 
 (SELECT CART_ID FROM CART_PRODUCTS WHERE NAME = 'Milk') as B
WHERE A.CART_ID = B.CART_ID
ORDER BY CART_ID

JOIN을 콤마 ','로 표현할 경우, WHERE로 연결 키 설정한다.

JOIN ON과 비슷한 효과를 내는 것 뿐이다.

사용된 명령어

filesort란?

ORDER BY, GROUP BY 처리에서 인덱스를 사용하지 못할 경우,
MySQL은 Filsort 알고리즘을 통해 데이터를 정렬한다.
되도록 filesort를 피하는 것이 좋다.

사용해야한다면, 불필요한 SELECT를 하는 column을 줄이자.

ref: https://ktdsoss.tistory.com/423