［EC-CUBE覚書］商品管理で規格の多い商品が重い件の修正案件メモ書き

2013年5月17日

EC-CUBE2系で商品の規格を登録する時に非常に重くなるという件で、他の方に依頼して、修正作業を近くで見せてもらいました。
以下はそのメモ書き。

修正案のベースはこちらのページ
EC-CUBE：商品管理で規格の多い商品の表示が遅い

やること
・MySQLで３つのviewを作成
・PHPの修正
　/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php の修正

/eccube/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.phpの修正

374行目あたり

return array(
            "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_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) ',

を以下のように修正

        return array(
            "vw_cross_class" => 'view_cross_class',

            "vw_cross_products_class" =>'view_cross_products_class',

ShellでMySQLの操作

といっても、私自身がShellでMySQLをいじったことがないので、このへん参考程度で。

最初にrootになっておきます。
mysqlに入る時は、mysqlのパスワードも必要。

[root@XXXX ]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is XXXXXX
Server version: 5.0.90-community MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use [データベース名];
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 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;

mysql> 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;

mysql> 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;

次回は自分でできるかな（＾＾；

EC-CUBE

Posted by ponnao