ec-cube2.4+MySQLで購入処理が遅い問題の対処

alkoshikawa 464views 更新:2013年10月1日

最新版では問題ないですが2.4系と2.11系ではソースコードがエボリューションしているため
ec-cubeのソースをいじっているとマイグレーションは至難の業です。
ec-cube2.4には商品数が増えると線形的に処理が重くなるという問題があります、
原因は「SC_DB_DBFactory_MYSQL.php」の「vw_product_class」というもので、
「dtb_products_class」レコードを全件取得してJOINなどをおこなっているため、
3000件超えたりしていると大変なことになります。

ロジックの見直しをしたいところですが、深くEC-CUBEに関わっていないと修正が正しいか判断できないため、
VIEWを使って速度の改善をおこないます。

/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php
こちらの「viewToSubQuery」を

"vw_product_class" => ' vw_product_class ',

に変更します。
VIEWを作成します。

CREATE VIEW `vw_T5` AS select `t1`.`product_class_id` AS `product_class_id`,
`t1`.`product_id` AS `product_id_sub`,
`t1`.`classcategory_id1` AS `classcategory_id1`,
`t1`.`classcategory_id2` AS `classcategory_id2`,
`t2`.`rank` AS `rank1`,
`t4`.`rank` AS `rank2`,
`t2`.`class_id` AS `class_id1`,
`t4`.`class_id` AS `class_id2`,
`t1`.`stock` AS `stock`,
`t1`.`price01` AS `price01`,
`t1`.`price02` AS `price02`,
`t1`.`stock_unlimited` AS `stock_unlimited`,
`t1`.`product_code` AS `product_code` from ((`dtb_products_class` `t1` left join `dtb_classcategory` `t2` on((`t1`.`classcategory_id1` = `t2`.`classcategory_id`))) left join `dtb_classcategory` `t4` on((`t1`.`classcategory_id2` = `t4`.`classcategory_id`)));
CREATE VIEW `vw_product_class` AS select `t5`.`product_class_id` AS `product_class_id`,
`t5`.`product_id_sub` AS `product_id_sub`,
`t5`.`classcategory_id1` AS `classcategory_id1`,
`t5`.`classcategory_id2` AS `classcategory_id2`,
`t5`.`rank1` AS `rank1`,
`t5`.`rank2` AS `rank2`,
`t5`.`class_id1` AS `class_id1`,
`t5`.`class_id2` AS `class_id2`,
`t5`.`stock` AS `stock`,
`t5`.`price01` AS `price01`,
`t5`.`price02` AS `price02`,
`t5`.`stock_unlimited` AS `stock_unlimited`,
`t5`.`product_code` AS `product_code`,
`t6`.`product_id` AS `product_id`,
`t6`.`name` AS `name`,
`t6`.`deliv_fee` AS `deliv_fee`,
`t6`.`sale_limit` AS `sale_limit`,
`t6`.`sale_unlimited` AS `sale_unlimited`,
`t6`.`category_id` AS `category_id`,
`t6`.`rank` AS `rank`,
`t6`.`status` AS `status`,
`t6`.`product_flag` AS `product_flag`,
`t6`.`point_rate` AS `point_rate`,
`t6`.`comment1` AS `comment1`,
`t6`.`comment2` AS `comment2`,
`t6`.`comment3` AS `comment3`,
`t6`.`comment4` AS `comment4`,
`t6`.`comment5` AS `comment5`,
`t6`.`comment6` AS `comment6`,
`t6`.`note` AS `note`,
`t6`.`file1` AS `file1`,
`t6`.`file2` AS `file2`,
`t6`.`file3` AS `file3`,
`t6`.`file4` AS `file4`,
`t6`.`file5` AS `file5`,
`t6`.`file6` AS `file6`,
`t6`.`main_list_comment` AS `main_list_comment`,
`t6`.`main_list_image` AS `main_list_image`,
`t6`.`main_comment` AS `main_comment`,
`t6`.`main_image` AS `main_image`,
`t6`.`main_large_image` AS `main_large_image`,
`t6`.`sub_title1` AS `sub_title1`,
`t6`.`sub_comment1` AS `sub_comment1`,
`t6`.`sub_image1` AS `sub_image1`,
`t6`.`sub_large_image1` AS `sub_large_image1`,
`t6`.`sub_title2` AS `sub_title2`,
`t6`.`sub_comment2` AS `sub_comment2`,
`t6`.`sub_image2` AS `sub_image2`,
`t6`.`sub_large_image2` AS `sub_large_image2`,
`t6`.`sub_title3` AS `sub_title3`,
`t6`.`sub_comment3` AS `sub_comment3`,
`t6`.`sub_image3` AS `sub_image3`,
`t6`.`sub_large_image3` AS `sub_large_image3`,
`t6`.`sub_title4` AS `sub_title4`,
`t6`.`sub_comment4` AS `sub_comment4`,
`t6`.`sub_image4` AS `sub_image4`,
`t6`.`sub_large_image4` AS `sub_large_image4`,
`t6`.`sub_title5` AS `sub_title5`,
`t6`.`sub_comment5` AS `sub_comment5`,
`t6`.`sub_image5` AS `sub_image5`,
`t6`.`sub_large_image5` AS `sub_large_image5`,
`t6`.`sub_title6` AS `sub_title6`,
`t6`.`sub_comment6` AS `sub_comment6`,
`t6`.`sub_image6` AS `sub_image6`,
`t6`.`sub_large_image6` AS `sub_large_image6`,
`t6`.`del_flg` AS `del_flg`,
`t6`.`creator_id` AS `creator_id`,
`t6`.`create_date` AS `create_date`,
`t6`.`update_date` AS `update_date`,
`t6`.`deliv_date_id` AS `deliv_date_id` from (`vw_T5` `t5` left join `dtb_products` `t6` on((`t5`.`product_id_sub` = `t6`.`product_id`)));

以上で購入処理部分が大分軽くなります。

参考:http://d.hatena.ne.jp/xross-cube/20101224/p1

 

 

ログイン / 新規登録してコメントする

このソースコードをストックして後で利用したり、作業に利用したソースコードをまとめることができます。

こちらもお役に立つかもしれません