SQL Analyzer


Explain Extended

ID Select Type Table Access Type Possible Keys Used Key Key Length Ref Rows Examined Filtered Extra
1 SIMPLE CT ref uid uid 64 const 1 100.00% Using index condition; Using where; Using temporary; Using filesort
1 SIMPLE G eq_ref PRIMARY PRIMARY 4 pravs.CT.goodsno 1 100.00%
1 SIMPLE O ref idx,ix_goodsno,idx_optno,idx_go_is_deleted,idx_goodsno_go_is_deleted_go_is_display,idx_goodsno_optno idx_optno 62 pravs.CT.optno 1 100.00% Using where
1 SIMPLE A eq_ref PRIMARY,idx PRIMARY 4 pravs.CT.addno 1 100.00% Using where
1 SIMPLE TG ref goodsno goodsno 4 pravs.CT.goodsno 1 100.00% Using where; Not exists
1 SIMPLE gp eq_ref PRIMARY PRIMARY 4 pravs.O.pchsno 1 100.00%

Original SQL Statement

SELECT 
  CT.optno, 
  CT.addno, 
  CT.ea, 
  CT.seq, 
  G.goodsno, 
  G.goodsnm, 
  G.img_s AS img, 
  G.img_i, 
  G.img_m, 
  G.img_l, 
  G.use_mobile_img, 
  G.img_x, 
  G.img_pc_x, 
  G.delivery_type, 
  G.goods_delivery, 
  G.use_emoney, 
  G.addoptnm, 
  G.runout, 
  G.usestock, 
  G.exclude_member_reserve, 
  G.exclude_member_discount, 
  G.sales_unit, 
  G.min_ea, 
  G.max_ea, 
  G.use_only_adult, 
  G.strprice, 
  G.todaygoods, 
  G.culture_benefit, 
  O.sno AS go_option_sno, 
  O.reserve, 
  O.price, 
  O.opt1, 
  O.opt2, 
  O.stock, 
  TG.tgsno, 
  TG.goodstype, 
  TG.usememberdc, 
  CT.c_addno_value, 
  gp.purchase_agent 
FROM 
  gd_cart AS CT 
  INNER JOIN gd_goods AS G ON CT.goodsno = G.goodsno 
  LEFT JOIN gd_goods_option AS O ON O.goodsno = CT.goodsno 
  AND O.optno = CT.optno 
  and go_is_deleted <> '1' 
  and go_is_display = '1' 
  LEFT JOIN gd_goods_add AS A ON A.goodsno = CT.goodsno 
  AND A.sno = CT.addno 
  AND CT.addno <> '' 
  LEFT JOIN gd_todayshop_goods AS TG ON CT.goodsno = TG.goodsno 
  left join gd_purchase as gp on gp.pchsno = O.pchsno 
WHERE 
  CT.uid = '0bac51a6a93ee2bc01da9142b7186ee7' 
  AND CT.is_buy = 0 
  AND CT.is_direct = 0 
  AND TG.tgsno IS NULL 
ORDER BY 
  G.delivery_type, 
  CT.regdt

Converted SQL Statement (MySQL Optimizer)

/* select#1 */
select 
  `pravs`.`CT`.`optno` AS `optno`, 
  `pravs`.`CT`.`addno` AS `addno`, 
  `pravs`.`CT`.`ea` AS `ea`, 
  `pravs`.`CT`.`seq` AS `seq`, 
  `pravs`.`G`.`goodsno` AS `goodsno`, 
  `pravs`.`G`.`goodsnm` AS `goodsnm`, 
  `pravs`.`G`.`img_s` AS `img`, 
  `pravs`.`G`.`img_i` AS `img_i`, 
  `pravs`.`G`.`img_m` AS `img_m`, 
  `pravs`.`G`.`img_l` AS `img_l`, 
  `pravs`.`G`.`use_mobile_img` AS `use_mobile_img`, 
  `pravs`.`G`.`img_x` AS `img_x`, 
  `pravs`.`G`.`img_pc_x` AS `img_pc_x`, 
  `pravs`.`G`.`delivery_type` AS `delivery_type`, 
  `pravs`.`G`.`goods_delivery` AS `goods_delivery`, 
  `pravs`.`G`.`use_emoney` AS `use_emoney`, 
  `pravs`.`G`.`addoptnm` AS `addoptnm`, 
  `pravs`.`G`.`runout` AS `runout`, 
  `pravs`.`G`.`usestock` AS `usestock`, 
  `pravs`.`G`.`exclude_member_reserve` AS `exclude_member_reserve`, 
  `pravs`.`G`.`exclude_member_discount` AS `exclude_member_discount`, 
  `pravs`.`G`.`sales_unit` AS `sales_unit`, 
  `pravs`.`G`.`min_ea` AS `min_ea`, 
  `pravs`.`G`.`max_ea` AS `max_ea`, 
  `pravs`.`G`.`use_only_adult` AS `use_only_adult`, 
  `pravs`.`G`.`strprice` AS `strprice`, 
  `pravs`.`G`.`todaygoods` AS `todaygoods`, 
  `pravs`.`G`.`culture_benefit` AS `culture_benefit`, 
  `pravs`.`O`.`sno` AS `go_option_sno`, 
  `pravs`.`O`.`reserve` AS `reserve`, 
  `pravs`.`O`.`price` AS `price`, 
  `pravs`.`O`.`opt1` AS `opt1`, 
  `pravs`.`O`.`opt2` AS `opt2`, 
  `pravs`.`O`.`stock` AS `stock`, 
  `pravs`.`TG`.`tgsno` AS `tgsno`, 
  `pravs`.`TG`.`goodstype` AS `goodstype`, 
  `pravs`.`TG`.`usememberdc` AS `usememberdc`, 
  `pravs`.`CT`.`c_addno_value` AS `c_addno_value`, 
  `pravs`.`gp`.`purchase_agent` AS `purchase_agent` 
from 
  `pravs`.`gd_cart` `CT` 
  join `pravs`.`gd_goods` `G` 
  left join `pravs`.`gd_goods_option` `O` on(
    (
      (
        `pravs`.`O`.`go_is_display` = '1'
      ) 
      and (
        `pravs`.`O`.`optno` = `pravs`.`CT`.`optno`
      ) 
      and (
        `pravs`.`O`.`goodsno` = `pravs`.`CT`.`goodsno`
      ) 
      and (
        `pravs`.`O`.`go_is_deleted` <> '1'
      )
    )
  ) 
  left join `pravs`.`gd_goods_add` `A` on(
    (
      (
        `pravs`.`A`.`goodsno` = `pravs`.`CT`.`goodsno`
      ) 
      and (
        `pravs`.`A`.`sno` = `pravs`.`CT`.`addno`
      ) 
      and (`pravs`.`CT`.`addno` <> '')
    )
  ) 
  left join `pravs`.`gd_todayshop_goods` `TG` on(
    (
      `pravs`.`CT`.`goodsno` = `pravs`.`TG`.`goodsno`
    )
  ) 
  left join `pravs`.`gd_purchase` `gp` on(
    (
      `pravs`.`gp`.`pchsno` = `pravs`.`O`.`pchsno`
    )
  ) 
where 
  (
    (
      `pravs`.`G`.`goodsno` = `pravs`.`CT`.`goodsno`
    ) 
    and (`pravs`.`CT`.`is_direct` = 0) 
    and (`pravs`.`CT`.`is_buy` = 0) 
    and (
      `pravs`.`CT`.`uid` = '0bac51a6a93ee2bc01da9142b7186ee7'
    ) 
    and isnull(`pravs`.`TG`.`tgsno`)
  ) 
order by 
  `pravs`.`G`.`delivery_type`, 
  `pravs`.`CT`.`regdt`