Experiment Detail
MySQL Performance Monitoring
2026. 11. 18.
● System ReadyExperiment Detail Information
최근 몇 달간 특정 가격 범위로 주문한 고객의 수
임의의 기간 동안 주문 금액이 특정 범위에 포함하는 고객의 수를 조회하는 쿼리의 인덱스 적용 전후의 성능을 측정 및 비교
SQL Query
Query 6
SELECT COUNT(*) FROM customer
INNER JOIN orders ON customer.customer_id = orders.customer_id
WHERE (orders.total_price BETWEEN 50000 AND 100000) AND
orders.order_date BETWEEN SUBDATE(NOW(), INTERVAL 3 MONTH) AND NOW();
Index Condition
Registered Index Conditions
Query 6 - Condition 13
인덱스 미적용
Query 6 - Condition 14
인덱스 적용
테이블명: orders
인덱스명: idx_orders_total_price
인덱스 컬럼: total_price
Query 6 - Condition 15
인덱스 적용
테이블명: orders
인덱스명: idx_order_date
인덱스 컬럼: order_date
Query 6 - Condition 16
인덱스 적용
테이블명: orders
인덱스명: idx_total_price_order_date
인덱스 컬럼: total_price, order_date
Query 6 - Condition 17
인덱스 적용
테이블명: orders
인덱스명: idx_orders_total_price
인덱스 컬럼: total_price
테이블명: orders
인덱스명: idx_order_date
인덱스 컬럼: order_date
Query 6
| 구분 | 평균 실행 시간 | 중앙값 |
|---|---|---|
| Before | 103.92 ms | 103.80 ms |
| After (Condition 14) | 89.33 ms | 89.47 ms |
| After (Condition 15) | 102.53 ms | 102.11 ms |
| After (Condition 16) | 44.10 ms | 44.04 ms |
| After (Condition 17) | 89.34 ms | 89.31 ms |
Query 6
| 구분 | 1회차 | 2회차 | 3회차 | 4회차 | 5회차 |
|---|---|---|---|---|---|
| Before | 104.25 ms | 103.80 ms | 104.23 ms | 103.62 ms | 103.72 ms |
| After (Condition 14) | 89.69 ms | 89.01 ms | 89.95 ms | 89.47 ms | 88.51 ms |
| After (Condition 15) | 104.60 ms | 102.20 ms | 102.11 ms | 101.82 ms | 101.94 ms |
| After (Condition 16) | 44.37 ms | 44.02 ms | 43.76 ms | 44.35 ms | 44.04 ms |
| After (Condition 17) | 89.71 ms | 89.64 ms | 89.31 ms | 89.17 ms | 88.88 ms |
Query 6
Before
EXPLAIN
orders
ALL
없음
299754
1.23%
상세 실행 계획 정보
1
SIMPLE
없음
없음
없음
fk_orders_customer_idx
Using where
customer
eq_ref
PRIMARY
1
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
performance_test_db.orders.customer_id
PRIMARY
Using index
EXPLAIN ANALYZE
-> Aggregate: count(0) (cost=28864 rows=1) (actual time=130..130 rows=1 loops=1)
-> Nested loop inner join (cost=28494 rows=3700) (actual time=2.19..129 rows=13597 loops=1)
-> Filter: ((orders.total_price between 50000 and 100000) and (orders.order_date between <cache>((now() - interval 3 month)) and <cache>(now()))) (cost=27199 rows=3700) (actual time=2.16..110 rows=13597 loops=1)
-> Table scan on orders (cost=27199 rows=299754) (actual time=1.55..90.4 rows=299940 loops=1)
-> Single-row covering index lookup on customer using PRIMARY (customer_id=orders.customer_id) (cost=0.25 rows=1) (actual time=0.00123..0.00126 rows=1 loops=13597)
After (Condition 14)
EXPLAIN
orders
range
idx_orders_total_price
81450
11.11%
상세 실행 계획 정보
1
SIMPLE
없음
4
없음
fk_orders_customer_idx,idx_orders_total_price
Using index condition; Using where
customer
eq_ref
PRIMARY
1
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
performance_test_db.orders.customer_id
PRIMARY
Using index
EXPLAIN ANALYZE
-> Aggregate: count(0) (cost=33485 rows=1) (actual time=202..202 rows=1 loops=1)
-> Nested loop inner join (cost=32580 rows=9049) (actual time=18.2..201 rows=13597 loops=1)
-> Filter: (orders.order_date between <cache>((now() - interval 3 month)) and <cache>(now())) (cost=29413 rows=9049) (actual time=18.2..185 rows=13597 loops=1)
-> Index range scan on orders using idx_orders_total_price over (50000 <= total_price <= 100000), with index condition: (orders.total_price between 50000 and 100000) (cost=29413 rows=81450) (actual time=18.2..182 rows=41214 loops=1)
-> Single-row covering index lookup on customer using PRIMARY (customer_id=orders.customer_id) (cost=0.25 rows=1) (actual time=0.00102..0.00104 rows=1 loops=13597)
After (Condition 15)
EXPLAIN
orders
ALL
없음
299754
5.56%
상세 실행 계획 정보
1
SIMPLE
없음
없음
없음
fk_orders_customer_idx,idx_order_date
Using where
customer
eq_ref
PRIMARY
1
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
performance_test_db.orders.customer_id
PRIMARY
Using index
EXPLAIN ANALYZE
-> Aggregate: count(0) (cost=37653 rows=1) (actual time=120..120 rows=1 loops=1)
-> Nested loop inner join (cost=35988 rows=16651) (actual time=1.48..120 rows=13597 loops=1)
-> Filter: ((orders.total_price between 50000 and 100000) and (orders.order_date between <cache>((now() - interval 3 month)) and <cache>(now()))) (cost=30160 rows=16651) (actual time=1.47..107 rows=13597 loops=1)
-> Table scan on orders (cost=30160 rows=299754) (actual time=1.46..89.2 rows=299940 loops=1)
-> Single-row covering index lookup on customer using PRIMARY (customer_id=orders.customer_id) (cost=0.25 rows=1) (actual time=760e-6..783e-6 rows=1 loops=13597)
After (Condition 16)
EXPLAIN
orders
range
idx_total_price_order_date
79056
11.11%
상세 실행 계획 정보
1
SIMPLE
없음
9
없음
fk_orders_customer_idx,idx_total_price_order_date
Using index condition
customer
eq_ref
PRIMARY
1
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
performance_test_db.orders.customer_id
PRIMARY
Using index
EXPLAIN ANALYZE
-> Aggregate: count(0) (cost=46555 rows=1) (actual time=50.6..50.6 rows=1 loops=1)
-> Nested loop inner join (cost=38650 rows=79056) (actual time=17.1..49.9 rows=13597 loops=1)
-> Index range scan on orders using idx_total_price_order_date over (50000 <= total_price <= 100000 AND '2026-08-18 07:38:29' <= order_date <= '2026-11-18 07:38:29'), with index condition: ((orders.total_price between 50000 and 100000) and (orders.order_date between <cache>((now() - interval 3 month)) and <cache>(now()))) (cost=28548 rows=79056) (actual time=17.1..34.5 rows=13597 loops=1)
-> Single-row covering index lookup on customer using PRIMARY (customer_id=orders.customer_id) (cost=0.25 rows=1) (actual time=977e-6..0.001 rows=1 loops=13597)
After (Condition 17)
EXPLAIN
orders
range
idx_orders_total_price
81450
50.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
없음
fk_orders_customer_idx,idx_orders_total_price,idx_order_date
Using index condition; Using where
customer
eq_ref
PRIMARY
1
100.0%
상세 실행 계획 정보
1
SIMPLE
없음
4
performance_test_db.orders.customer_id
PRIMARY
Using index
EXPLAIN ANALYZE
-> Aggregate: count(0) (cost=54979 rows=1) (actual time=101..101 rows=1 loops=1)
-> Nested loop inner join (cost=50907 rows=40725) (actual time=14.4..100 rows=13597 loops=1)
-> Filter: (orders.order_date between <cache>((now() - interval 3 month)) and <cache>(now())) (cost=36653 rows=40725) (actual time=13.7..83.2 rows=13597 loops=1)
-> Index range scan on orders using idx_orders_total_price over (50000 <= total_price <= 100000), with index condition: (orders.total_price between 50000 and 100000) (cost=36653 rows=81450) (actual time=13.7..80.7 rows=41214 loops=1)
-> Single-row covering index lookup on customer using PRIMARY (customer_id=orders.customer_id) (cost=0.25 rows=1) (actual time=0.00105..0.00107 rows=1 loops=13597)