Index and Execution Plan

-- 쿼리 A: 인덱스가 있는 customer_id로 검색
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 100;
-- 결과: SEARCH TABLE orders USING INDEX idx_orders_customer_id (customer_id=?)

-- 쿼리 B: 인덱스가 없는 notes로 LIKE 검색
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE notes LIKE '%급히 배송%';
-- 결과: SCAN TABLE orders

SELECT
    tbl_name  AS table_name,
    name      AS index_name,
    sql       AS create_sql
FROM sqlite_master
WHERE type = 'index'
  AND sql IS NOT NULL  -- 자동 생성 인덱스(PK 등) 제외
ORDER BY tbl_name, name;

SELECT
    tbl_name       AS table_name,
    COUNT(*)       AS index_count
FROM sqlite_master
WHERE type = 'index'
  AND sql IS NOT NULL
GROUP BY tbl_name
ORDER BY index_count DESC;

-- 1) 인덱스 생성 전 실행 계획
EXPLAIN QUERY PLAN
SELECT * FROM payments WHERE method = 'card';
-- 결과: SCAN TABLE payments

-- 2) 인덱스 생성
CREATE INDEX idx_payments_method ON payments(method);

-- 3) 인덱스 생성 후 실행 계획
EXPLAIN QUERY PLAN
SELECT * FROM payments WHERE method = 'card';
-- 결과: SEARCH TABLE payments USING INDEX idx_payments_method (method=?)

EXPLAIN QUERY PLAN
SELECT o.order_number, p.name, oi.quantity, oi.unit_price
FROM orders AS o
INNER JOIN order_items AS oi ON o.id = oi.order_id
INNER JOIN products AS p ON oi.product_id = p.id
WHERE o.ordered_at LIKE '2024-12%'
  AND o.status = 'delivered';

-- 커버링 인덱스 생성
CREATE INDEX idx_orders_cust_date_amount
    ON orders(customer_id, ordered_at, total_amount);

-- 실행 계획 확인
EXPLAIN QUERY PLAN
SELECT ordered_at, total_amount
FROM orders
WHERE customer_id = 100;
-- 결과: SEARCH TABLE orders USING COVERING INDEX idx_orders_cust_date_amount (customer_id=?)

-- 부분 인덱스 생성
CREATE INDEX idx_products_active_category
    ON products(category_id)
    WHERE is_active = 1;

-- 부분 인덱스를 사용하는 쿼리
EXPLAIN QUERY PLAN
SELECT name, price
FROM products
WHERE category_id = 5
  AND is_active = 1;
-- 결과: SEARCH TABLE products USING INDEX idx_products_active_category (category_id=?)

-- 조건이 없으면 부분 인덱스 미사용
EXPLAIN QUERY PLAN
SELECT name, price
FROM products
WHERE category_id = 5;
-- 결과: SEARCH TABLE products USING INDEX idx_products_category_id (category_id=?)

-- 인덱스 생성
CREATE INDEX idx_orders_status_date ON orders(status, ordered_at);
CREATE INDEX idx_orders_date_status ON orders(ordered_at, status);

-- 쿼리 A: status 등호 + ordered_at 범위
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'delivered' AND ordered_at >= '2024-01-01';
-- SQLite 옵티마이저가 idx_orders_status_date를 선택 (status 등호 후 ordered_at 범위 스캔)

-- 쿼리 B: ordered_at 범위만
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE ordered_at >= '2024-01-01';
-- idx_orders_date_status 또는 idx_orders_ordered_at 사용 (첫 번째 칼럼이 ordered_at)

SELECT
    COUNT(*)                                           AS total_rows,
    COUNT(DISTINCT status)                             AS distinct_status,
    COUNT(DISTINCT customer_id)                        AS distinct_customer_id,
    ROUND(1.0 * COUNT(DISTINCT status) / COUNT(*), 6) AS selectivity_status,
    ROUND(1.0 * COUNT(DISTINCT customer_id) / COUNT(*), 6) AS selectivity_customer
FROM orders;

-- 쿼리 A: 함수 적용 → 인덱스 미사용
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE SUBSTR(ordered_at, 1, 4) = '2024';
-- 결과: SCAN TABLE orders
-- 이유: SUBSTR() 함수가 칼럼에 적용되어 인덱스 활용 불가

-- SARGable 대안:
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01';
-- 결과: SEARCH TABLE orders USING INDEX idx_orders_ordered_at

-- 쿼리 B: OR 조건
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 100 OR status = 'cancelled';
-- 결과: SCAN TABLE orders (또는 MULTI-INDEX OR)
-- 이유: 두 조건이 별개 인덱스 → 옵티마이저가 전체 스캔 선택 가능

-- 쿼리 C: 부정 조건
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id != 100;
-- 결과: SCAN TABLE orders
-- 이유: 거의 모든 행을 반환 → 전체 스캔이 더 효율적

-- 개선: JOIN으로 변환
SELECT
    p.name,
    p.price,
    COALESCE(oi_stats.order_count, 0) AS order_count,
    oi_stats.avg_rating
FROM products AS p
LEFT JOIN (
    SELECT
        oi.product_id,
        COUNT(*)       AS order_count,
        AVG(r.rating)  AS avg_rating
    FROM order_items AS oi
    LEFT JOIN reviews AS r ON oi.product_id = r.product_id
    GROUP BY oi.product_id
) AS oi_stats ON p.id = oi_stats.product_id
WHERE p.is_active = 1
ORDER BY order_count DESC
LIMIT 20;

-- 실행 계획 비교
-- 원본 (서브쿼리): SCAN TABLE products + 행마다 CORRELATED SCALAR SUBQUERY 2회
-- 개선 (JOIN): SCAN TABLE products + SCAN TABLE order_items (1회) + LEFT JOIN reviews (1회)

-- 개선 A: 범위 조건으로 변환
SELECT * FROM orders
WHERE ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01';
-- idx_orders_ordered_at 인덱스 사용 가능

-- 개선 B: 계산을 비교 값 쪽으로 이동
SELECT * FROM products
WHERE price > 1000000 / 0.9;  -- price > 1111111.11
-- idx_products_xxx 인덱스 사용 가능

-- 개선 C: 접두사 검색으로 변경 (가능한 경우)
SELECT * FROM customers
WHERE name LIKE '김%';  -- '김'으로 시작하는 고객
-- idx_customers_name 인덱스 사용 가능 (접두사 LIKE)

-- 중간 검색이 반드시 필요하면 → FTS(전문 검색)나 별도 검색 칼럼 고려

-- 방식 A: IN
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE id IN (SELECT DISTINCT customer_id FROM reviews);

-- 방식 B: EXISTS
EXPLAIN QUERY PLAN
SELECT * FROM customers AS c
WHERE EXISTS (SELECT 1 FROM reviews AS r WHERE r.customer_id = c.id);

-- orders 테이블의 인덱스 목록
SELECT name, sql
FROM sqlite_master
WHERE type = 'index'
  AND tbl_name = 'orders'
  AND sql IS NOT NULL
ORDER BY name;

-- 1) 실행 계획 확인 (원본)
EXPLAIN QUERY PLAN
SELECT
    cat.name AS category,
    strftime('%Y-%m', o.ordered_at) AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items AS oi,
     orders AS o,
     products AS p,
     categories AS cat
WHERE oi.order_id = o.id
  AND oi.product_id = p.id
  AND p.category_id = cat.id
  AND strftime('%Y', o.ordered_at) = '2024'
  AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY cat.name, strftime('%Y-%m', o.ordered_at)
ORDER BY cat.name, month;
-- 문제점: strftime() 함수로 인해 orders 테이블 풀 스캔

-- 2) 최적화된 쿼리
SELECT
    cat.name AS category,
    SUBSTR(o.ordered_at, 1, 7) AS month,
    COUNT(DISTINCT o.id) AS order_count,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders AS o
INNER JOIN order_items AS oi ON o.id = oi.order_id
INNER JOIN products AS p ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE o.ordered_at >= '2024-01-01'
  AND o.ordered_at < '2025-01-01'
  AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY cat.name, SUBSTR(o.ordered_at, 1, 7)
ORDER BY cat.name, month;

-- 3) 최적화 후 실행 계획 확인
EXPLAIN QUERY PLAN
SELECT
    cat.name AS category,
    SUBSTR(o.ordered_at, 1, 7) AS month,
    COUNT(DISTINCT o.id) AS order_count,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders AS o
INNER JOIN order_items AS oi ON o.id = oi.order_id
INNER JOIN products AS p ON oi.product_id = p.id
INNER JOIN categories AS cat ON p.category_id = cat.id
WHERE o.ordered_at >= '2024-01-01'
  AND o.ordered_at < '2025-01-01'
  AND o.status NOT IN ('cancelled', 'returned', 'return_requested')
GROUP BY cat.name, SUBSTR(o.ordered_at, 1, 7)
ORDER BY cat.name, month;