|
아래와 같은 쿼리가 있는데요 이상태로 실행시키면 속도가 나쁘지 않습니다.
select * from morning_buyer_table
where register_date >= 1199113200
AND buyer_store = 0
and (
(
buyer_sess LIKE '%주영%' OR
buyer_name1 LIKE '%주영%' OR
buyer_name2 LIKE '%주영%' OR
buyer_name3 LIKE '%주영%' OR
REPLACE(REPLACE(buyer_tel1,' ',''),'-','') LIKE '%주영%' OR
REPLACE(REPLACE(buyer_tel2,' ',''),'-','') LIKE '%주영%' OR
REPLACE(REPLACE(buyer_tel3,' ',''),'-','') LIKE '%주영%' OR
REPLACE(REPLACE(buyer_tel4,' ',''),'-','') LIKE '%주영%' OR
buyer_main LIKE '%주영%'
)
OR
buyer_sess in
(
select sess
from ecm_memo_cs_new
where (comment LIKE '%주영%' OR comment_manager LIKE '%주영%')
)
OR
buyer_sess in
(
select sess
from ecm_memo
where (comment LIKE '%주영%')
)
) ORDER BY register_date DESC LIMIT 0, 100
그런데 페이징 처리를 위해 total row수를 알기 위해 쿼리문을 아래처럼 변경하면 속도가 많이 느려집니다.
select count(*) from morning_buyer_table
where register_date >= 1266591600
AND buyer_store = 0
and (
(
buyer_sess LIKE '%주영%' OR
buyer_name1 LIKE '%주영%' OR
buyer_name2 LIKE '%주영%' OR
buyer_name3 LIKE '%주영%' OR
REPLACE(REPLACE(buyer_tel1,' ',''),'-','') LIKE '%주영%' OR
REPLACE(REPLACE(buyer_tel2,' ',''),'-','') LIKE '%주영%' OR
REPLACE(REPLACE(buyer_tel3,' ',''),'-','') LIKE '%주영%' OR
REPLACE(REPLACE(buyer_tel4,' ',''),'-','') LIKE '%주영%' OR
buyer_main LIKE '%주영%'
)
OR
buyer_sess in
(
select sess
from ecm_memo_cs_new
where (comment LIKE '%주영%' OR comment_manager LIKE '%주영%')
)
OR
buyer_sess in
(
select sess
from ecm_memo
where (comment LIKE '%주영%')
)
) LIMIT 1
속도를 개선하기 위한 방법은 어떤게 있을가요? 도통모르겟습니다....
아래는 카운트 쿼리 explain 입니다.
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
| 1 |
PRIMARY |
morning_buyer_table |
range |
register_date |
register_date |
4 |
|
104783 |
Using where |
| 3 |
DEPENDENT SUBQUERY |
ecm_memo |
unique_subquery |
sess |
sess |
32 |
func |
1 |
Using where |
| 2 |
DEPENDENT SUBQUERY |
ecm_memo_cs_new |
index_subquery |
sess |
sess |
32 |
func |
1 |
Using where |
morning_buyer_table에는 287864개의 rows
ecm_memo에는 67903개의 rows
ecm_memo_cs_new에는 39644개의 rows가 있습니다.
|
