Changeset 21433 for branches

Timestamp:

2012/02/03 11:07:25 (12 years ago)

Author:

Seasoft

Message:

#1626 (SQLチューニングによる商品一覧処理の高速化)

Location:

branches/version-2_12-dev/data/class

Files:

• SC_Product.php (modified) (2 diffs)
• pages/products/LC_Page_Products_List.php (modified) (4 diffs)

branches/version-2_12-dev/data/class/SC_Product.php

@@ -75 +75 @@
         $table = <<< __EOS__
             dtb_products AS alldtl
-            LEFT JOIN dtb_products_class
-                ON alldtl.product_id = dtb_products_class.product_id
-            LEFT JOIN dtb_product_categories
-                ON alldtl.product_id = dtb_product_categories.product_id
 __EOS__;
         $objQuery->setGroupBy('alldtl.product_id');
@@ -118 +114 @@
         $table = <<< __EOS__
             dtb_products AS alldtl
-            LEFT JOIN dtb_products_class
-                ON alldtl.product_id = dtb_products_class.product_id
-            LEFT JOIN dtb_product_categories
-                ON alldtl.product_id = dtb_product_categories.product_id
-__EOS__;
-        $objQuery->setGroupBy('alldtl.product_id');
-        $sql_base = $objQuery->getSql('alldtl.product_id',$table);
-        return $objQuery->getOne( "SELECT count(*) FROM ( $sql_base ) as t" , $arrVal);
+__EOS__;
+        return $objQuery->count($table, '', $arrVal);
     }
 

branches/version-2_12-dev/data/class/pages/products/LC_Page_Products_List.php

@@ -269 +269 @@
                 if (strlen($searchCondition["where_category"]) >= 1) {
                     $dtb_product_categories = "(SELECT * FROM dtb_product_categories WHERE ".$searchCondition["where_category"].")";
-                    $arrval_order           = array_merge($searchCondition['arrvalCategory'], $searchCondition['arrvalCategory']);
+                    $arrval_order           = $searchCondition['arrvalCategory'];
                 } else {
                     $dtb_product_categories = 'dtb_product_categories';
@@ -276 +276 @@
                     (
                         SELECT
-                             T3.rank
+                             T3.rank * 2147483648 + T2.rank
                         FROM
                             $dtb_product_categories T2
@@ -285 +285 @@
                         LIMIT 1
                     ) DESC
-                    ,(
-                        SELECT
-                            T2.rank
-                        FROM
-                            $dtb_product_categories T2
-                            JOIN dtb_category T3
-                                USING (category_id)
-                        WHERE T2.product_id = alldtl.product_id
-                        ORDER BY T3.rank DESC, T2.rank DESC
-                        LIMIT 1
-                    ) DESC
-                    ,product_id
+                    ,product_id DESC
 __EOS__;
                     $objQuery->setOrder($order);
@@ -425 +414 @@
 
         if (strlen($searchCondition["where_category"]) >= 1) {
-            $searchCondition['where'] .= " AND dtb_product_categories.".$searchCondition["where_category"];
+            $searchCondition['where'] .= ' AND EXISTS (SELECT * FROM dtb_product_categories WHERE ' . $searchCondition["where_category"] . ' AND product_id = alldtl.product_id)';
             $searchCondition['arrval'] = array_merge($searchCondition['arrval'], $searchCondition['arrvalCategory']);
         }