Hatena::ブログ(Diary)

EC-CUBEのカスタマイズならクロスキューブ!サイト制作メモ このページをアンテナに追加 RSSフィード Twitter

2010-12-24

EC-CUBEをチューニング - MySQL:MySQLでもviewを使って8倍速にするカスタマイズ!!

さて、だいぶ間が開いてしまいましたが、前回の続きで、EC-CUBEMySQLで動かす際のチューニングの続きです。
僕がやった施策の中ではこれが劇的に効果がありました。

今回は、MySQLでもviewを使うという物です。

データベースviewとは、簡単い言うとSELECT文の結果をテーブルの様に扱える機能という事です。

EC-CUBEは、PostgreSQLで動かす場合は、商品情報カテゴリや規格の情報をまとめた物をviewにして、商品一覧画面や商品詳細画面等で利用しています。
ところが、EC-CUBEMySQLで動かす場合MySQLバージョンが4.1でも利用出来る様に作られているため、viewの部分をサブクエリにして動かしています。

この、ネストしたサブクエリからSELECTする場合インデックスが使えないので、こんなに遅くなっていて、インデックス張っても意味が無い訳です。

バージョン5.0からはMySQLでもviewが使える様になっています。最近レンタルサーバであれば5.0以上を導入しているところも多いので、バージョン5.0以上なら導入可能です。

8.25倍速くなった!

結果から言うと、/products/list.php*1 が8.25倍速くなりました!

実はちょこちょこ問題もあるので、微修正は必要ですが、そんな事も苦にならないくらいの激速っぷりです!!

体感的にも劇的な改善です。

一応ちょっと負荷を上げた状態でのabテストの結果も貼っておきます。

Document Path:          /eccube244/html/products/list.php
Document Length:        90587 bytes

Concurrency Level:      5
Time taken for tests:   13.157 seconds
Complete requests:      10
Failed requests:        0
Write errors:           0
Total transferred:      909540 bytes
HTML transferred:       905870 bytes
Requests per second:    0.76 [#/sec] (mean)
Time per request:       6578.478 [ms] (mean)
Time per request:       1315.696 [ms] (mean, across all concurrent requests)
Transfer rate:          67.51 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    1   0.9      2       2
Processing:  1728 5980 3188.8   5578   13155
Waiting:     1323 4816 3472.3   4640   13145
Total:       1728 5981 3189.5   5580   13157
WARNING: The median and mean for the initial connection time are not within a normal deviation
        These results are probably not that reliable.

Percentage of the requests served within a certain time (ms)
  50%   5580
  66%   7296
  75%   7300
  80%   7530
  90%  13157
  95%  13157
  98%  13157
  99%  13157
 100%  13157 (longest request)


で、実際にどうするかですが、ここからはクロスキューブからクリスマスプレゼントです。

EC-CUBEMySQL場合に処理を分けている箇所を変更する

EC-CUBEMySQLでの処理を書いている所が、

/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php
です。

ここを継承している

/data/class_extends/db/dbfactory/SC_DB_DBFactory_MYSQL_Ex.php
を以下の物に書き換えます。

<?php
// {{{ requires
require_once(CLASS_PATH . "db/dbfactory/SC_DB_DBFactory_MYSQL.php");

class SC_DB_DBFactory_MYSQL_Ex extends SC_DB_DBFactory_MYSQL {
    function viewToSubQuery() {
        return array(
            "vw_cross_class" => ' vw_cross_class',

            "vw_cross_products_class" =>' vw_cross_products_class ',

            "vw_products_nonclass" => ' vw_products_nonclass ',

            "vw_products_allclass" => " vw_products_allclass ",

            "vw_products_allclass_detail" => " vw_products_allclass_detail ",

            "vw_product_class" => ' vw_product_class ',

            "vw_category_count" => ' vw_category_count '
        );
    }
}
?>

やっている事は、viewをサブクエリに置き換えている所を削除して、そのままviewを使う様にしています。

EC-CUBEで利用しているMySQLデータベースviewを追加する

次に、データベースviewを追加します。

MySQLではviewのfrom部分にサブクエリは使えないので、PostgreSQLを使う場合とは少し違っています。

下記のSQLをダーっと流し込んで下さい。

--
-- ビュー用の構造 `vw_T2`
--

CREATE VIEW `vw_T2` AS select `dtb_products_class`.`product_id` AS `product_id_sub`,
`dtb_products_class`.`product_code` AS `product_code`,
`dtb_products_class`.`price01` AS `price01`,
`dtb_products_class`.`price02` AS `price02`,
`dtb_products_class`.`stock` AS `stock`,
`dtb_products_class`.`stock_unlimited` AS `stock_unlimited`,
`dtb_products_class`.`classcategory_id1` AS `classcategory_id1`,
`dtb_products_class`.`classcategory_id2` AS `classcategory_id2` from `dtb_products_class` where ((`dtb_products_class`.`classcategory_id1` = 0) and (`dtb_products_class`.`classcategory_id2` = 0));

-- --------------------------------------------------------

--
-- ビュー用の構造 `vw_T4`
--

CREATE VIEW `vw_T4` AS select `dtb_products_class`.`product_id` AS `product_id`,
min(`dtb_products_class`.`product_code`) AS `product_code_min`,
max(`dtb_products_class`.`product_code`) AS `product_code_max`,
min(`dtb_products_class`.`price01`) AS `price01_min`,
max(`dtb_products_class`.`price01`) AS `price01_max`,
min(`dtb_products_class`.`price02`) AS `price02_min`,
max(`dtb_products_class`.`price02`) AS `price02_max`,
min(`dtb_products_class`.`stock`) AS `stock_min`,
max(`dtb_products_class`.`stock`) AS `stock_max`,
min(`dtb_products_class`.`stock_unlimited`) AS `stock_unlimited_min`,
max(`dtb_products_class`.`stock_unlimited`) AS `stock_unlimited_max`,
count(0) AS `class_count` from `dtb_products_class` group by `dtb_products_class`.`product_id`;

-- --------------------------------------------------------

--
-- ビュー用の構造 `vw_T5`
--

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`)));


--
-- ビュー用の構造 `vw_category_count`
--

CREATE VIEW `vw_category_count` AS select `t1`.`category_id` AS `category_id`,
`t1`.`category_name` AS `category_name`,
`t1`.`parent_category_id` AS `parent_category_id`,
`t1`.`level` AS `level`,
`t1`.`rank` AS `rank`,
`t2`.`product_count` AS `product_count` from (`dtb_category` `t1` left join `dtb_category_total_count` `t2` on((`t1`.`category_id` = `t2`.`category_id`)));

-- --------------------------------------------------------

--
-- ビュー用の構造 `vw_cross_class`
--

CREATE VIEW `vw_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` `t1` join `dtb_classcategory` `t2`);

-- --------------------------------------------------------

--
-- ビュー用の構造 `vw_cross_products_class`
--

CREATE VIEW `vw_cross_products_class` AS select `t1`.`class_id1` AS `class_id1`,
`t1`.`class_id2` AS `class_id2`,
`t1`.`classcategory_id1` AS `classcategory_id1`,
`t1`.`classcategory_id2` AS `classcategory_id2`,
`t2`.`product_id` AS `product_id`,
`t1`.`name1` AS `name1`,
`t1`.`name2` AS `name2`,
`t2`.`product_code` AS `product_code`,
`t2`.`stock` AS `stock`,
`t2`.`price01` AS `price01`,
`t2`.`price02` AS `price02`,
`t1`.`rank1` AS `rank1`,
`t1`.`rank2` AS `rank2` from (`vw_cross_class` `t1` left join `dtb_products_class` `t2` on(((`t1`.`classcategory_id1` = `t2`.`classcategory_id1`) and (`t1`.`classcategory_id2` = `t2`.`classcategory_id2`))));

-- --------------------------------------------------------
--
-- ビュー用の構造 `vw_products_allclass_detail`
--

CREATE VIEW `vw_products_allclass_detail` AS select `dtb_products`.`product_id` AS `product_id`,
`dtb_products`.`name` AS `name`,
`dtb_products`.`deliv_fee` AS `deliv_fee`,
`dtb_products`.`sale_limit` AS `sale_limit`,
`dtb_products`.`rank` AS `rank`,
`dtb_products`.`status` AS `status`,
`dtb_products`.`product_flag` AS `product_flag`,
`dtb_products`.`point_rate` AS `point_rate`,
`dtb_products`.`sale_unlimited` AS `sale_unlimited`,
`dtb_products`.`comment1` AS `comment1`,
`dtb_products`.`comment2` AS `comment2`,
`dtb_products`.`comment3` AS `comment3`,
`dtb_products`.`comment4` AS `comment4`,
`dtb_products`.`comment5` AS `comment5`,
`dtb_products`.`comment6` AS `comment6`,
`dtb_products`.`note` AS `note`,
`dtb_products`.`file1` AS `file1`,
`dtb_products`.`file2` AS `file2`,
`dtb_products`.`file3` AS `file3`,
`dtb_products`.`file4` AS `file4`,
`dtb_products`.`file5` AS `file5`,
`dtb_products`.`file6` AS `file6`,
`dtb_products`.`main_list_comment` AS `main_list_comment`,
`dtb_products`.`main_list_image` AS `main_list_image`,
`dtb_products`.`main_comment` AS `main_comment`,
`dtb_products`.`main_image` AS `main_image`,
`dtb_products`.`main_large_image` AS `main_large_image`,
`dtb_products`.`sub_title1` AS `sub_title1`,
`dtb_products`.`sub_comment1` AS `sub_comment1`,
`dtb_products`.`sub_image1` AS `sub_image1`,
`dtb_products`.`sub_large_image1` AS `sub_large_image1`,
`dtb_products`.`sub_title2` AS `sub_title2`,
`dtb_products`.`sub_comment2` AS `sub_comment2`,
`dtb_products`.`sub_image2` AS `sub_image2`,
`dtb_products`.`sub_large_image2` AS `sub_large_image2`,
`dtb_products`.`sub_title3` AS `sub_title3`,
`dtb_products`.`sub_comment3` AS `sub_comment3`,
`dtb_products`.`sub_image3` AS `sub_image3`,
`dtb_products`.`sub_large_image3` AS `sub_large_image3`,
`dtb_products`.`sub_title4` AS `sub_title4`,
`dtb_products`.`sub_comment4` AS `sub_comment4`,
`dtb_products`.`sub_image4` AS `sub_image4`,
`dtb_products`.`sub_large_image4` AS `sub_large_image4`,
`dtb_products`.`sub_title5` AS `sub_title5`,
`dtb_products`.`sub_comment5` AS `sub_comment5`,
`dtb_products`.`sub_image5` AS `sub_image5`,
`dtb_products`.`sub_large_image5` AS `sub_large_image5`,
`dtb_products`.`sub_title6` AS `sub_title6`,
`dtb_products`.`sub_comment6` AS `sub_comment6`,
`dtb_products`.`sub_image6` AS `sub_image6`,
`dtb_products`.`sub_large_image6` AS `sub_large_image6`,
`dtb_products`.`del_flg` AS `del_flg`,
`dtb_products`.`creator_id` AS `creator_id`,
`dtb_products`.`create_date` AS `create_date`,
`dtb_products`.`update_date` AS `update_date`,
`dtb_products`.`deliv_date_id` AS `deliv_date_id`,
`t4`.`product_code_min` AS `product_code_min`,
`t4`.`product_code_max` AS `product_code_max`,
`t4`.`price01_min` AS `price01_min`,
`t4`.`price01_max` AS `price01_max`,
`t4`.`price02_min` AS `price02_min`,
`t4`.`price02_max` AS `price02_max`,
`t4`.`stock_min` AS `stock_min`,
`t4`.`stock_max` AS `stock_max`,
`t4`.`stock_unlimited_min` AS `stock_unlimited_min`,
`t4`.`stock_unlimited_max` AS `stock_unlimited_max`,
`t4`.`class_count` AS `class_count` from (`dtb_products` left join `vw_T4` `t4` on((`dtb_products`.`product_id` = `t4`.`product_id`)));

-- --------------------------------------------------------

--
-- ビュー用の構造 `vw_products_allclass`
--

CREATE VIEW `vw_products_allclass` AS select `alldtl`.`product_id` AS `product_id`,
`alldtl`.`name` AS `name`,
`alldtl`.`deliv_fee` AS `deliv_fee`,
`alldtl`.`sale_limit` AS `sale_limit`,
`alldtl`.`rank` AS `rank`,
`alldtl`.`status` AS `status`,
`alldtl`.`product_flag` AS `product_flag`,
`alldtl`.`point_rate` AS `point_rate`,
`alldtl`.`sale_unlimited` AS `sale_unlimited`,
`alldtl`.`comment1` AS `comment1`,
`alldtl`.`comment2` AS `comment2`,
`alldtl`.`comment3` AS `comment3`,
`alldtl`.`comment4` AS `comment4`,
`alldtl`.`comment5` AS `comment5`,
`alldtl`.`comment6` AS `comment6`,
`alldtl`.`note` AS `note`,
`alldtl`.`file1` AS `file1`,
`alldtl`.`file2` AS `file2`,
`alldtl`.`file3` AS `file3`,
`alldtl`.`file4` AS `file4`,
`alldtl`.`file5` AS `file5`,
`alldtl`.`file6` AS `file6`,
`alldtl`.`main_list_comment` AS `main_list_comment`,
`alldtl`.`main_list_image` AS `main_list_image`,
`alldtl`.`main_comment` AS `main_comment`,
`alldtl`.`main_image` AS `main_image`,
`alldtl`.`main_large_image` AS `main_large_image`,
`alldtl`.`sub_title1` AS `sub_title1`,
`alldtl`.`sub_comment1` AS `sub_comment1`,
`alldtl`.`sub_image1` AS `sub_image1`,
`alldtl`.`sub_large_image1` AS `sub_large_image1`,
`alldtl`.`sub_title2` AS `sub_title2`,
`alldtl`.`sub_comment2` AS `sub_comment2`,
`alldtl`.`sub_image2` AS `sub_image2`,
`alldtl`.`sub_large_image2` AS `sub_large_image2`,
`alldtl`.`sub_title3` AS `sub_title3`,
`alldtl`.`sub_comment3` AS `sub_comment3`,
`alldtl`.`sub_image3` AS `sub_image3`,
`alldtl`.`sub_large_image3` AS `sub_large_image3`,
`alldtl`.`sub_title4` AS `sub_title4`,
`alldtl`.`sub_comment4` AS `sub_comment4`,
`alldtl`.`sub_image4` AS `sub_image4`,
`alldtl`.`sub_large_image4` AS `sub_large_image4`,
`alldtl`.`sub_title5` AS `sub_title5`,
`alldtl`.`sub_comment5` AS `sub_comment5`,
`alldtl`.`sub_image5` AS `sub_image5`,
`alldtl`.`sub_large_image5` AS `sub_large_image5`,
`alldtl`.`sub_title6` AS `sub_title6`,
`alldtl`.`sub_comment6` AS `sub_comment6`,
`alldtl`.`sub_image6` AS `sub_image6`,
`alldtl`.`sub_large_image6` AS `sub_large_image6`,
`alldtl`.`del_flg` AS `del_flg`,
`alldtl`.`creator_id` AS `creator_id`,
`alldtl`.`create_date` AS `create_date`,
`alldtl`.`update_date` AS `update_date`,
`alldtl`.`deliv_date_id` AS `deliv_date_id`,
`alldtl`.`product_code_min` AS `product_code_min`,
`alldtl`.`product_code_max` AS `product_code_max`,
`alldtl`.`price01_min` AS `price01_min`,
`alldtl`.`price01_max` AS `price01_max`,
`alldtl`.`price02_min` AS `price02_min`,
`alldtl`.`price02_max` AS `price02_max`,
`alldtl`.`stock_min` AS `stock_min`,
`alldtl`.`stock_max` AS `stock_max`,
`alldtl`.`stock_unlimited_min` AS `stock_unlimited_min`,
`alldtl`.`stock_unlimited_max` AS `stock_unlimited_max`,
`alldtl`.`class_count` AS `class_count`,
`dtb_category`.`rank` AS `category_rank`,
`t2`.`category_id` AS `category_id`,
`t2`.`rank` AS `product_rank` from ((`vw_products_allclass_detail` `alldtl` left join `dtb_product_categories` `t2` on((`alldtl`.`product_id` = `t2`.`product_id`))) left join `dtb_category` on((`t2`.`category_id` = `dtb_category`.`category_id`)));

-- --------------------------------------------------------


--
-- ビュー用の構造 `vw_products_nonclass`
--

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

-- --------------------------------------------------------

--
-- ビュー用の構造 `vw_product_class`
--

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`)));

-- --------------------------------------------------------

ちょっとゲップが出るSQLですね。


さあ、これでOKです。実際にApacheベンチとかで試してみて下さい。劇的に速度が改善されているハズです。

Merry Christmas ~

*1:全件一覧表示

質問質問 2011/01/03 18:24 ビュー用の構造 `vw_products_allclass_detail`から下のSQL文を挿入すると以下のエラーが出ますが、私だけでしょうか。。。
どうしたら普通に入力が出来ますか????
#1050 - Table 'vw_products_allclass_detail' already existsというエラーが出ます。

xross-cubexross-cube 2011/01/04 16:33 何回か試してませんか?既にviewが出来てる様です。いったんすべてのviewを削除してから再度流してみてください。

質問質問 2011/01/07 12:29 上記適用後に自分で追加したテーブルの内容(※)が表示されなくなってしまったのですがSC_DB_DBFactory_MYSQL_Ex.phpとSQLに何か変更が必要なのでしょうか?

※モバイル用の商品詳細コメント等

質問質問 2011/01/07 12:32 上記適用後に自分で新たに追加して表示させていたテーブルの内容(※)が表示されなくなってしまったのですがSC_DB_DBFactory_MYSQL_Ex.phpとSQLになにか追加や変更が必要でしたでしょうか?

※モバイル用の詳細コメント等

xross-cubexross-cube 2011/01/09 02:56 viewの定義に、標準には無い項目を追加する必要があります。

shop_brishop_bri 2011/01/18 21:57 MySQLで8倍早くなるチューニングを本当にありがとうございました。以前より、格段に早くなり本当に素敵なプレゼントでした。
ひとつこれが原因かどうか解らないのですが、以前出来ていたバックアップデータの作成が出来なくなり、真っ白の画面になってしまいます。DBとかphpとかの知識がなくそれを確かめるすべもなくどうしたらよいものか困っております。もし、アドバイスなどありましたら、教えていただけないでしょうか?

質問質問 2011/01/28 16:25 EC-CUBEのチューニングを行いたく、こちらの記事を拝見しましたが、DBに疎く、初歩的な内容かもしれませんが、質問させて下さい。
phpMyAdminを利用してview作成する場合は「SQL」に上記を貼り付けて実行すれば良いのでしょうか?

実際に試してみたところ
#1356 - View 'データベース名.vw_cross_class' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
というエラーが出てしまうのですが、回避策をご存知でしたらご教示頂けますと幸いです。

xross-cubexross-cube 2011/01/28 19:30 申し訳ございません。
この記事は基本的に投げっぱなしの記事になっておりますので、個別案件へのお問い合わせには対応しかねる事をご理解願います。

ありがとうございます!ありがとうございます! 2012/08/15 20:29 ずっと、簡単にチューニングできる方法を探していて、今日たどり着きました。 高速化の業者さんに見積もりしたら、20万といわれていたのでこんな形でできたのが本当にうれしいです。 ありがとうございました。
おっしゃるとおり、8倍早くなりました。

投稿したコメントは管理者が承認するまで公開されません。

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証

トラックバック - http://d.hatena.ne.jp/xross-cube/20101224/p1