2010年3月25日木曜日

 

【EC-CUBE】MySQLのVIEWで商品管理の規格表示を改善する

追記:2010/03/31
商品管理ページの規格が表示できずタイムアウトしていたのが(サイト自体が接続不可能になることもしばしば)、数秒で表示できるようになったので劇的な変化と言えると思う。
規格登録してる商品の価格を変更するには「商品マスタ」で商品個々の「規格」から編集するしかない。

EC-CUBE:商品管理で規格の多い商品の表示が遅い - 神宮球場近くで働くSEのブログ

「/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php」の「vw_cross_class」と「vw_cross_products_class」がボトルネックになるようで、ビューを作成して処理を軽くする。

/* view_cross_class */
create view view_cross_class as 
  SELECT
      T1.class_id AS class_id1,
      T2.class_id AS class_id2,
      T1.classcategory_id AS classcategory_id1,
      T2.classcategory_id AS classcategory_id2,
      T1.name AS name1,
      T2.name AS name2,
      T1.rank AS rank1,
      T2.rank AS rank2
    FROM dtb_classcategory AS T1, dtb_classcategory AS T2;
/* view_cross_products_class で利用する view_cross_products_class_sub */
create view view_cross_products_class_sub as
  SELECT
      T1.class_id AS class_id1,
      T2.class_id AS class_id2,
      T1.classcategory_id AS classcategory_id1,
      T2.classcategory_id AS classcategory_id2,
      T1.name AS name1,
      T2.name AS name2,
      T1.rank AS rank1,
      T2.rank AS rank2
    FROM dtb_classcategory AS T1, dtb_classcategory AS T2;
/* view_cross_products_class */

create view view_cross_products_class as
  SELECT T1.class_id1, T1.class_id2, T1.classcategory_id1, T1.classcategory_id2, T2.product_id, T1.name1, T1.name2, T2.product_code, T2.stock, T2.price01, T2.price02, T1.rank1, T1.rank2
    FROM view_cross_products_class_sub AS
      T1 LEFT JOIN dtb_products_class AS
        T2 ON T1.classcategory_id1 = T2.classcategory_id1 AND T1.classcategory_id2 = T2.classcategory_id2;

これらを使って「/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php」の「vw_cross_class」と「vw_cross_products_class」を修正する。

/* 「/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php」の修正「vw_cross_class」 */
// 修正前

"vw_cross_class" => '(
    SELECT
      T1.class_id AS class_id1,
      T2.class_id AS class_id2,
      T1.classcategory_id AS classcategory_id1,
      T2.classcategory_id AS classcategory_id2,
      T1.name AS name1,
      T2.name AS name2,
      T1.rank AS rank1,
      T2.rank AS rank2
    FROM dtb_classcategory AS T1, dtb_classcategory AS T2
)'
// 修正後

"vw_cross_class" => 'view_cross_class'
/* 「/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php」の修正「vw_cross_products_class」 */
// 修正前

"vw_cross_products_class" =>'(
      SELECT T1.class_id1, T1.class_id2, T1.classcategory_id1, T1.classcategory_id2, T2.product_id, T1.name1, T1.name2, T2.product_code, T2.stock, T2.price01, T2.price02, T1.rank1, T1.rank2
        FROM ( 
            SELECT
                  T1.class_id AS class_id1,
                  T2.class_id AS class_id2,
                  T1.classcategory_id AS classcategory_id1,
                  T2.classcategory_id AS classcategory_id2,
                  T1.name AS name1,
                  T2.name AS name2,
                  T1.rank AS rank1,
                  T2.rank AS rank2
             FROM dtb_classcategory AS T1, dtb_classcategory AS T2 
        ) AS
        T1 LEFT JOIN dtb_products_class AS T2 ON
             T1.classcategory_id1 = T2.classcategory_id1
               AND T1.classcategory_id2 = T2.classcategory_id2) '
// 修正後

"vw_cross_products_class" => 'view_cross_products_class'

関連

【EC-CUBE】商品表示のSQLクエリを改善する - さぼてん[2009/04/03]

ラベル:


コメント: コメントを投稿

登録 コメントの投稿 [Atom]



この投稿へのリンク:

リンクを作成



<< ホーム

This page is powered by Blogger. Isn't yours?

登録 投稿 [Atom]

Google