mysql5 버전에서 view 테이블을 구성하고 select를 수행할 경우 속도가 너무 느려 이렇게 질문을 드립니다.

일반적인 view의 경우는 아무런 문제가 되지 않습니다만..

create ALGORITHM=merge view bd_contents_view as
select
cm_domain_key as dc_domainkey,
bs_search_all as dc_search_all,
bs_search_type as dc_search_type,
bs_search_none as dc_search_none,
bs_catelist as dc_catelist,
bs_fullcate as dc_fullcate,
ct_dir as dc_dir,
ct_bid as dc_bid,
ct_hid as dc_hid,
ct_idx as dc_cid,
abs(ct_group) as dc_group,
ct_order as dc_order,
ct_subject as dc_subject,
concat_ws(',.,:', ct_content, (select GROUP_CONCAT(cmt_content SEPARATOR ',.,:') from comment use index (list) where cmt_cid=ct_idx)) as dc_content,
ct_tags as dc_tags,
DATE_FORMAT(ct_reg_date, '%Y%m%d%H%i%s') as dc_regdate,
ct_x as dc_x,
ct_y as dc_y,
ct_delete as dc_delete,
ct_penname as dc_penname,
ct_ext_field as dc_ext,
ct_uid as dc_uid,
ct_img as dc_img
from contents use index (PRIMARY)
left join board use index (PRIMARY) on (ct_bid = bs_idx)
left join community use index (PRIMARY) on (bs_cid = cm_idx)
where ct_idx>0 and bs_search_none != 'all' and (bs_search_type like "bd%" or bs_search_type like "fore%") and ct_delete="n";

위와같이 view를 생성할 경우 엄청난 속도 저하가 발생합니다. 

쿼리를 보시면 서브쿼리등의 문제로 속도가 빠르면 이상하지 않은가? 하고 생각 하시는 분들이 있으리라 생각됩니다만... ㅋ

문제는 view로 구성하지 않고 아래와 같이 동일하게 select는 속도가 느리지 않습니다.

select
cm_domain_key as dc_domainkey,
bs_search_all as dc_search_all,
bs_search_type as dc_search_type,
bs_search_none as dc_search_none,
bs_catelist as dc_catelist,
bs_fullcate as dc_fullcate,
ct_dir as dc_dir,
ct_bid as dc_bid,
ct_hid as dc_hid,
ct_idx as dc_cid,
abs(ct_group) as dc_group,
ct_order as dc_order,
ct_subject as dc_subject,
concat_ws(',.,:', ct_content, (select GROUP_CONCAT(cmt_content SEPARATOR ',.,:') from comment use index (list) where cmt_cid=ct_idx)) as dc_content,
ct_tags as dc_tags,
DATE_FORMAT(ct_reg_date, '%Y%m%d%H%i%s') as dc_regdate,
ct_x as dc_x,
ct_y as dc_y,
ct_delete as dc_delete,
ct_penname as dc_penname,
ct_ext_field as dc_ext,
ct_uid as dc_uid,
ct_img as dc_img
from contents use index (PRIMARY)
left join board use index (PRIMARY) on (ct_bid = bs_idx)
left join community use index (PRIMARY) on (bs_cid = cm_idx)
where ct_idx>0 and bs_search_none != 'all' and (bs_search_type like "bd%" or bs_search_type like "fore%") and ct_delete="n";

동일한 쿼리를 view로 구성해서 select 한것과 그냥 select 한 것이 왜 차이가 나는지 모르겠네요.. ㅋ


mysql> explain select * from bd_contents_view;
+----+--------------------+------------+--------+---------------+---------+---------+---------------------+------+-------------+
| id | select_type        | table      | type   | possible_keys | key     | key_len | ref                 | rows | Extra       |
+----+--------------------+------------+--------+---------------+---------+---------+---------------------+------+-------------+
|  1 | PRIMARY            | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL                | 1431 |             |
|  2 | DERIVED            | contents   | range  | PRIMARY,list  | PRIMARY | 4       | NULL                |  533 | Using where |
|  2 | DERIVED            | board      | eq_ref | PRIMARY       | PRIMARY | 4       | sem.contents.ct_bid |    1 | Using where |
|  2 | DERIVED            | community  | eq_ref | PRIMARY       | PRIMARY | 4       | sem.board.bs_cid    |    1 |             |
|  3 | DEPENDENT SUBQUERY | comment    | ref    | list          | list    | 4       | sem.contents.ct_idx |    1 |             |
+----+--------------------+------------+--------+---------------+---------+---------+---------------------+------+-------------+

mysql> explain select cm_domain_key as dc_domainkey, bs_search_all as dc_search_all, bs_search_type as dc_search_type, bs_search_none as dc_search_none, bs_catelist as dc_catelist, bs_fullcate as dc_fullcate, ct_dir as dc_dir, ct_bid as dc_bid, ct_hid as dc_hid, ct_idx as dc_cid, abs(ct_group) as dc_group, ct_order as dc_order, ct_subject as dc_subject, concat_ws(',.,:', ct_content, (select GROUP_CONCAT(cmt_content SEPARATOR ',.,:') from comment use index (list) where cmt_cid=ct_idx)) as dc_content, ct_tags as dc_tags, DATE_FORMAT(ct_reg_date, '%Y%m%d%H%i%s') as dc_regdate, ct_x as dc_x, ct_y as dc_y, ct_delete as dc_delete, ct_penname as dc_penname, ct_ext_field as dc_ext, ct_uid as dc_uid, ct_img as dc_img from contents use index (PRIMARY) left join board use index (PRIMARY) on (ct_bid = bs_idx) left join community use index (PRIMARY) on (bs_cid = cm_idx) where ct_idx>0 and bs_search_none != 'all' and (bs_search_type like "bd%" or bs_search_type like "fore%") and ct_delete="n";
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------+-------+-------------+
| id | select_type        | table     | type   | possible_keys | key     | key_len | ref                 | rows  | Extra       |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------+-------+-------------+
|  1 | PRIMARY            | contents  | range  | PRIMARY       | PRIMARY | 4       | NULL                | 64725 | Using where |
|  1 | PRIMARY            | board     | eq_ref | PRIMARY       | PRIMARY | 4       | sem.contents.ct_bid |     1 | Using where |
|  1 | PRIMARY            | community | eq_ref | PRIMARY       | PRIMARY | 4       | sem.board.bs_cid    |     1 |             |
|  2 | DEPENDENT SUBQUERY | comment   | ref    | list          | list    | 4       | sem.contents.ct_idx |     1 |             |
+----+--------------------+-----------+--------+---------------+---------+---------+---------------------+-------+-------------+


두개의 explain 결과의 차이를 보면 view로 구성 후엔 DERIVED가 나타나면서 파생되었다고 알려주는데.. 이게 원인인 듯합니다만...

이 부분을 어떻게 하면 해결할 수 있을까요?

view의 ALGORITHM 도 변경을 해봤으나 소용이 없었습니다. ㅜㅜ

중간에 서브쿼리를 제거하면 해결이 되긴합니다만... 이해할 수 없는 것은 동일한 쿼리를 view로 구성했을 뿐인데 이렇게 차이가 나는 이유입니다.

google에서 검색을 해봐도 해결안은 보이질 않네요..

혹시 관련 내용을 아시는 분이 계시면 조그만 조언이라도 알려주시면 감사하겠습니다. ^^;