1차분류, 2차분류, 3차분류 로 구분하여 상품을 48,000개 등록하였습니다.

1차분류 : 생활용품(상품 수 : 약 9,000개)

2차분류 : 1차분류의 생활용품에 해당하는 분류가 약 54개

1차 분류인 생활용품 링크(/shop/list.php)를 클릭하면

모든 생황용품이 페이징되어 보여지는데, 그 이전에 생활용품에 대한 하위 카테고리별 상품수량이 보여지도록 되어있습니다.

차량용품 850    |     티슈 186    |    액자  234

위의 2차분류별 상품 수량을 보여주는 페이지(listcategory.skin.php)의 내용을 보면 쿼리가 아래와 같이 루프를 돌더군요.

Select ca_id, ca_name from main_shop_category where ca_id like '10%' and length(ca_id) = 2 and ca_use = '1' order by ca_order, ca_id;   <= 1차분류의 ca_id와 ca_name을 가져옴.

그런 다음 1차분류의 하위카테고리의 수 만큼(33개) 상품 등록수를 가져오는 쿼리가 루프를 돌면서 수행되더라구요.

select count(it_id) as cnt from main_shop_item where (ca_id like '1010%' or ca_id2 like '1010%' or ca_id3 like '1010%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '1020%' or ca_id2 like '1020%' or ca_id3 like '1020%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '1030%' or ca_id2 like '1030%' or ca_id3 like '1030%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '1040%' or ca_id2 like '1040%' or ca_id3 like '1040%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '1050%' or ca_id2 like '1050%' or ca_id3 like '1050%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '1060%' or ca_id2 like '1060%' or ca_id3 like '1060%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '1070%' or ca_id2 like '1070%' or ca_id3 like '1070%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '1080%' or ca_id2 like '1080%' or ca_id3 like '1080%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '1090%' or ca_id2 like '1090%' or ca_id3 like '1090%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10a0%' or ca_id2 like '10a0%' or ca_id3 like '10a0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10b0%' or ca_id2 like '10b0%' or ca_id3 like '10b0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10c0%' or ca_id2 like '10c0%' or ca_id3 like '10c0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10d0%' or ca_id2 like '10d0%' or ca_id3 like '10d0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10e0%' or ca_id2 like '10e0%' or ca_id3 like '10e0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10f0%' or ca_id2 like '10f0%' or ca_id3 like '10f0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10g0%' or ca_id2 like '10g0%' or ca_id3 like '10g0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10h0%' or ca_id2 like '10h0%' or ca_id3 like '10h0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10i0%' or ca_id2 like '10i0%' or ca_id3 like '10i0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10j0%' or ca_id2 like '10j0%' or ca_id3 like '10j0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10k0%' or ca_id2 like '10k0%' or ca_id3 like '10k0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10l0%' or ca_id2 like '10l0%' or ca_id3 like '10l0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10m0%' or ca_id2 like '10m0%' or ca_id3 like '10m0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10n0%' or ca_id2 like '10n0%' or ca_id3 like '10n0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10o0%' or ca_id2 like '10o0%' or ca_id3 like '10o0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10p0%' or ca_id2 like '10p0%' or ca_id3 like '10p0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10q0%' or ca_id2 like '10q0%' or ca_id3 like '10q0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10r0%' or ca_id2 like '10r0%' or ca_id3 like '10r0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10s0%' or ca_id2 like '10s0%' or ca_id3 like '10s0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10t0%' or ca_id2 like '10t0%' or ca_id3 like '10t0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10u0%' or ca_id2 like '10u0%' or ca_id3 like '10u0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10v0%' or ca_id2 like '10v0%' or ca_id3 like '10v0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10w0%' or ca_id2 like '10w0%' or ca_id3 like '10w0%') and it_use = '1';
select count(it_id) as cnt from main_shop_item where (ca_id like '10x0%' or ca_id2 like '10x0%' or ca_id3 like '10x0%') and it_use = '1';

1회 수행될 때마다 

$str .= '<li><a href="./list.php?ca_id='.$row['ca_id'].'">'.$row['ca_name'].' ('.$row2['cnt'].')</a></li>';

이렇게 2차분류명과 2차분류에 대한 상품수를 표시하더군요.

그런데 이 페이지(/shop/list.php)를 맨 처음 수행할때는 페이지가 열리는 시간이 10초 안팎으로 너무 느립니다.

그 다음 다시 동일한 1차 카테고리인 생활용품을 클릭하면 속도가 빠릅니다.

캐쉬때문에 빠른가 싶지만 위의 쿼리는 데이터를 가져오는 쿼리가 루프도는게 아니라 카운트만 가져오는건데 이렇게 느릴 수 없다는게 제 생각입니다.

그래서 위의 코드만 제거해 보고 상품 리스팅만 해보았는데 속도가 아 주빠릅니다.

즉 위의 코드에서 발생하는 문제라 보이는데 인덱싱을 함에도 불구하고 느리다는게 더 문제인듯합니다.

총 상품 수가 5만개 가량 되어 위의 쿼리를 인덱스 태우게 하기 위해 ca_id, ca_id2, ca_id3, it_use필드를 인덱스로 묶어주었는데 약 20%정도의 속도향상은 있은데 그래도 로딩 속도가 5초이상이니 좀 심각한 상황이네요;;

shop_item과 category테이블을 innoDB로 변경하고 시도해 보기도 했는데 기존보다는 속도향상이 30% 정도 좋아진 것 밖에 없어 다시 myIsam으로 되돌렸네요;;

처음으로 해당 페이지가 로딩될때만 지독하게 느립니다.

해결 방법 없을까요????