【EC-CUBE】最近購入された商品ブロック追加プラグインを高速化

EC-CUBEのDBログファイルを見てみる。
ん、遅い。何かが遅い。
このSQLが遅い。

SQL: SELECT dtb_order_detail.product_id,
dtb_order_detail.product_name,
dtb_order.create_date,
dtb_order.order_pref,
(SELECT main_list_image FROM dtb_products WHERE dtb_products.product_id = dtb_order_detail.product_id) as main_list_image FROM dtb_order_detail LEFT JOIN dtb_order ON dtb_order_detail.order_id = dtb_order.order_id WHERE dtb_order.del_flg = 0 ORDER BY dtb_order.create_date DESC LIMIT 10 OFFSET 0
PlaceHolder: array (
)
execution time: 2.07 sec

平均2~4秒かかってる。遅い。
調べてみると、「最近購入された商品ブロック追加プラグイン」のSQLだとわかった。

とりあえずEXPLAINしてみる。

Limit  (cost=366701.88..366701.91 rows=10 width=46) (actual time=1575.197..1575.242 rows=10 loops=1)
  ->  Sort  (cost=366701.88..366849.30 rows=58969 width=46) (actual time=1575.192..1575.208 rows=10 loops=1)
        Sort Key: dtb_order.create_date
        ->  Hash Join  (cost=3212.48..360686.42 rows=58969 width=46) (actual time=129.716..1158.385 rows=57967 loops=1)
              Hash Cond: ("outer".order_id = "inner".order_id)
              ->  Seq Scan on dtb_order_detail  (cost=0.00..1746.47 rows=61447 width=40) (actual time=0.157..126.893 rows=61022 loops=1)
              ->  Hash  (cost=3051.68..3051.68 rows=20320 width=14) (actual time=129.270..129.270 rows=17256 loops=1)
                    ->  Seq Scan on dtb_order  (cost=0.00..3051.68 rows=20320 width=14) (actual time=1.322..87.515 rows=17256 loops=1)
                          Filter: (del_flg = 0)
              SubPlan
                ->  Index Scan using dtb_products_pkey on dtb_products  (cost=0.00..5.99 rows=1 width=28) (actual time=0.005..0.007 rows=1 loops=57967)
                      Index Cond: (product_id = $0)
Total runtime: 1579.201 ms

やはり1.5秒かかってる。Sort遅っ。

とりあえず、受注日と受注のあった県は表示しないので、dtb_order.create_dateとdtb_order.order_prefは削除
ソートも受注日(dtb_order.create_date)から受注詳細番号に変更(dtb_order_detail.order_detail_id)

SELECT dtb_order_detail.product_id,
dtb_order_detail.product_name,
(SELECT main_list_image FROM dtb_products WHERE dtb_products.product_id = dtb_order_detail.product_id) as main_list_image FROM dtb_order_detail LEFT JOIN dtb_order ON dtb_order_detail.order_id = dtb_order.order_id WHERE dtb_order.del_flg = 0 ORDER BY dtb_order_detail.order_detail_id DESC LIMIT 10 OFFSET 0

無事めちゃ早くなりました。

Limit  (cost=0.00..92.04 rows=10 width=40) (actual time=54.607..55.230 rows=10 loops=1)
  ->  Nested Loop  (cost=0.00..542766.73 rows=58969 width=40) (actual time=54.601..55.191 rows=10 loops=1)
        ->  Index Scan Backward using dtb_order_detail_pkey on dtb_order_detail  (cost=0.00..2017.26 rows=61447 width=44) (actual time=20.640..20.664 rows=10 loops=1)
        ->  Index Scan using dtb_order_pkey on dtb_order  (cost=0.00..3.04 rows=1 width=4) (actual time=3.398..3.400 rows=1 loops=10)
              Index Cond: ("outer".order_id = dtb_order.order_id)
              Filter: (del_flg = 0)
        SubPlan
          ->  Index Scan using dtb_products_pkey on dtb_products  (cost=0.00..5.99 rows=1 width=28) (actual time=0.037..0.039 rows=1 loops=10)
                Index Cond: (product_id = $0)
Total runtime: 55.400 ms