【EC-CUBE】商品一覧高速化

こちらのサイトhttp://nayuzak.blog50.fc2.com/blog-entry-37.htmlで、「DBにMySQLを使っているときに、商品管理画面の規格表示に時間が掛る時がある。

色々条件を調べてみると、商品数が問題ではなく 商品に紐付く規格が多くなると表示が遅くなるみたい。」ということでviewを作成していたので商品一覧の表示にも有効なのかな？と思い適用。

1．まず view作成

商品 の 規格1 * 規格2 を取得しているクエリの中で vw_cross_class と vw_cross_products_class をview生成。

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;

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;

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;

2．次に /data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php の修正

"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'

＠もう一か所

"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'

体感として、ほんの少し早くなったかも…