wataru
sw2w-****@asahi*****
2005年 2月 2日 (水) 07:29:13 JST
ちょっと困った状態になりましたので御質問させて頂きたいと思います。
共用サーバーでOSCOMMERCEを運営しているのですが昨日、MySQL処理によるCPUの占有
により、サーバー側からアカウントをサスペンドされてしまいました。
その時の状況をSQLの状況をテキストでもらったのですが、テキストには
Query | 4 | Copying to tmp table | select distinct
p.products_id, pd.products_name, sum(op.products_quantity) as
products_quantity from |
がずらっと並んでおり、このクエリはベストセラーの集計のものだとわかりました。
ベストセラーの集計はカスタマイズして期間を２ヶ月間にするようにしています。
このベストセラーの集計ってサーバーに負荷を与えるものなのでしょうか？また集計
期間を短くすれば改善できるものなのでしょうか？それとも負荷を与えている原因が
ベストセラーではなく他にあったりするんるんでしょうか。御存知の方御教授頂けれ
ばと思います。
catalog/includes/boxes/best_sellers.phpの変更点は以下です。
[変更前]
if (isset($current_category_id) && ($current_category_id > 0)) {
$best_sellers_query = tep_db_query("select distinct p.products_id,
pd.products_name from " . TABLE_PRODUCTS . " p, " .
TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c,
" . TABLE_CATEGORIES . " c where p.products_status = '1' and
p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id
= '" . $languages_id . "' and p.products_id = p2c.products_id and
p2c.categories_id = c.categories_id and '" . $current_category_id . "' in
(c.categories_id, c.parent_id) order by p.products_ordered desc,
pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
} else {
$best_sellers_query = tep_db_query("select distinct p.products_id,
pd.products_name from " . TABLE_PRODUCTS . " p, " .
TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and
p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id
= '" . $languages_id . "' order by p.products_ordered desc, pd.products_name
limit " . MAX_DISPLAY_BESTSELLERS);
}
[変更後]
if (isset($current_category_id) && ($current_category_id > 0)) {
$best_sellers_query = tep_db_query("select distinct p.products_id,
pd.products_name, sum(op.products_quantity) as products_quantity from " .
TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " .
TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op where op.products_id =
p.products_id and op.orders_id = o.orders_id and o.date_purchased >=
DATE_SUB(now(), INTERVAL 30 DAY) and p.products_status = '1' and
p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id
= '" . $languages_id . "' and p.products_id = p2c.products_id and
p2c.categories_id = c.categories_id and '" . $current_category_id . "' in
(c.categories_id, c.parent_id) group by op.products_id order by
products_quantity desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
} else {
$best_sellers_query = tep_db_query("select distinct p.products_id,
pd.products_name, sum(op.products_quantity) as products_quantity from " .
TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " .
TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op where op.products_id =
p.products_id and op.orders_id = o.orders_id and o.date_purchased >=
DATE_SUB(now(), INTERVAL 60 DAY) and p.products_status = '1' and
p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id
= '" . $languages_id . "' group by op.products_id order by products_quantity
desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
}
杉本渉
sw2w-****@asahi*****
-------------- next part --------------
HTMLの添付ファイルを保管しました...
다운로드