SQL Analyzer


Explain Extended

ID Select Type Table Access Type Possible Keys Used Key Key Length Ref Rows Examined Filtered Extra
1 SIMPLE O range PRIMARY,orddt orddt 6 1 100.00% Using where; Using index; Using temporary; Using filesort
1 SIMPLE OI ref idx idx 8 pravs.O.ordno 1 100.00%
1 SIMPLE G eq_ref PRIMARY,idx_open_goodsno,open PRIMARY 4 pravs.OI.goodsno 1 100.00% Using where

Original SQL Statement

SELECT 
  OI.goodsno, 
  OI.goodsnm, 
  COUNT(OI.goodsno) AS `goods_cnt` 
FROM 
  gd_order as O FORCE INDEX (PRIMARY, orddt) STRAIGHT_JOIN gd_order_item AS OI FORCE INDEX (idx) ON O.ordno = OI.ordno 
  INNER JOIN gd_goods AS G ON OI.goodsno = G.goodsno 
WHERE 
  O.orddt >= DATE_SUB(NOW(), INTERVAL 1 HOUR) 
  AND G.open = 1 
GROUP BY 
  OI.goodsno 
ORDER BY 
  `goods_cnt` DESC 
LIMIT 
  20

Converted SQL Statement (MySQL Optimizer)

/* select#1 */
select 
  `pravs`.`OI`.`goodsno` AS `goodsno`, 
  `pravs`.`OI`.`goodsnm` AS `goodsnm`, 
  count(`pravs`.`OI`.`goodsno`) AS `goods_cnt` 
from 
  `pravs`.`gd_order` `O` FORCE INDEX (`orddt`) FORCE INDEX (PRIMARY) straight_join `pravs`.`gd_order_item` `OI` FORCE INDEX (`idx`) 
  join `pravs`.`gd_goods` `G` 
where 
  (
    (
      `pravs`.`OI`.`ordno` = `pravs`.`O`.`ordno`
    ) 
    and (
      `pravs`.`G`.`goodsno` = `pravs`.`OI`.`goodsno`
    ) 
    and (`pravs`.`G`.`open` = 1) 
    and (
      `pravs`.`O`.`orddt` >= < cache >(
        (now() - interval 1 hour)
      )
    )
  ) 
group by 
  `pravs`.`OI`.`goodsno` 
order by 
  `goods_cnt` desc 
limit 
  20