mysql에서는 빠른데 큐브리드에서 쿼리를 포문으로 돌려 여러번 실행시 너무 오래 걸립니다.

by 초보 posted Mar 13, 2010


 select count(wr_id) as cnt from board_write where wr_category like '%10'
 select count(wr_id) as cnt from board_write where wr_category like '%1010'
                                             :
                                             :
                                             :
 select count(wr_id) as cnt from board_write where wr_category like '%10010'

 위와같은 쿼리 100개정도를 한페이지에서 포문으로 돌려 실행시 mysql 에서는 로딩속도가 2초정도 걸렸는데

  큐브리드에서는 10초정도 걸립니다. 스키마는 다음과 같습니다.

  CREATE TABLE "board_write"(
"wr_id" numeric(20,0) DEFAULT 0 NOT NULL,
"bo_id" numeric(10,0) DEFAULT 0 NOT NULL,
"wr_num" numeric(20,0) NOT NULL,
"wr_reply" character varying(40) NOT NULL,
"wr_parent" numeric(10,0) DEFAULT 0 NOT NULL,
"wr_option" character varying(200) NOT NULL,
"wr_subject" character varying(510) NOT NULL,
"wr_preview" character varying(1000),
"wr_link1" character varying(510),
"wr_link2" character varying(510),
"wr_link1_hit" integer DEFAULT 0 NOT NULL,
"wr_link2_hit" integer DEFAULT 0 NOT NULL,
"wr_trackback" character varying(510),
"wr_hit" integer DEFAULT 0 NOT NULL,
"wr_good" integer DEFAULT 0 NOT NULL,
"wr_nogood" integer DEFAULT 0 NOT NULL,
"wr_comment" numeric(10,0) DEFAULT 0,
"mb_id" character varying(40) NOT NULL,
"wr_password" character varying(100) NOT NULL,
"wr_name" character varying(510) NOT NULL,
"wr_email" character varying(510) NOT NULL,
"wr_homepage" character varying(510),
"wr_time" timestamp NOT NULL,
"wr_last" timestamp NOT NULL,
"wr_ip" character varying(60) NOT NULL,
"wr_1" character varying(510),
"wr_2" character varying(510),
"wr_3" character varying(510),
"wr_4" character varying(510),
"wr_5" character varying(510),
"wr_6" character varying(510),
"wr_7" character varying(510),
"wr_8" character varying(510),
"wr_9" character varying(510),
"wr_10" character varying(510),
"wr_category" character varying(510)
);
CREATE  INDEX "wr_num_reply_parent" ON "nb_board_write"("wr_num","wr_reply","wr_parent");
CREATE  INDEX "wr_board_id" ON "nb_board_write"("bo_id","wr_id");
CREATE  INDEX ON "nb_board_write"("wr_subject","wr_name","wr_time","wr_9","wr_category");

  데이타 건수는 10만건 정도 됩니다.

  어떻게 해야 하죠??
 
 


Articles

28 29 30 31 32 33 34 35 36 37