这条sql有没优化余地

[ecshop528]

explain select sum(if(is_hot = 1,1,0) ) as is_hot,sum(if(is_new = 1,1,0) ) as is_new,sum(if(is_best = 1,1,0) ) as is_best ,sum(if(goods_number <= warn_number ,1,0)) as warn ,sum(if(is_alone_sale=1,1,0)) as total FROM `icmall`.`ecs_goods` WHERE is_delete = 0 AND is_real = 1


1 SIMPLE ecs_goods ref delete_real_goods_id,all_info all_info 2 const,const 2302644


以下是这个表的索引情况
ecs_goods 0 PRIMARY 1 goods_id A 2302656 BTREE
ecs_goods 1 cat_id 1 cat_id A 850 BTREE
ecs_goods 1 brand_id 1 brand_id A 348 BTREE
ecs_goods 1 goods_type 1 goods_type A 1 BTREE
ecs_goods 1 goods_name 1 goods_name A 2302656 BTREE
ecs_goods 1 brand_goods_id 1 brand_goods_id A 2302656 BTREE
ecs_goods 1 delete_real_goods_id 1 is_delete A 1 BTREE
ecs_goods 1 delete_real_goods_id 2 is_real A 1 BTREE
ecs_goods 1 delete_real_goods_id 3 goods_id A 2302656 BTREE


现在的查询时间。大概是 10秒，大概有2302715条数据。

[rucypli]

sql语句没什么优化余地 只能从业务方面来尽量避免这种统计 或者尽量在程序端尽量缓存这种结果

[romanitc]

都是高手呀！

[ecshop528]

引用 楼主 ecshop528 的回复:

本帖最后由 ecshop528 于 2012-11-26 11:43:42 编辑 explain select sum(if(is_hot = 1,1,0) ) as is_hot,sum(if(is_new = 1,1,0) ) as is_new,sum(if(is_best = 1,1,0) ) as is_best ,sum(if(……

对。暂时只有想到了缓存。呵呵。。 也就是没其他解决方案了 /

[Rotel-刘志东]

列出表结构及表间关系。

[ecshop528]

引用 4 楼 lzd_83 的回复:

列出表结构及表间关系。

CREATE TABLE `ecs_goods` ( `goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `cat_id` smallint(5) unsigned NOT NULL DEFAULT '0', `goods_sn` varchar(60) NOT NULL DEFAULT '', `goods_name` varchar(120) NOT NULL DEFAULT '', `goods_name_style` varchar(60) NOT NULL DEFAULT '+', `click_count` int(10) unsigned NOT NULL DEFAULT '0', `brand_id` smallint(5) unsigned NOT NULL DEFAULT '0', `provider_name` varchar(100) NOT NULL DEFAULT '', `goods_number` smallint(5) unsigned NOT NULL DEFAULT '0', `goods_weight` decimal(10,3) unsigned NOT NULL DEFAULT '0.000', `market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00', `shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00', `promote_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00', `promote_start_date` int(11) unsigned NOT NULL DEFAULT '0', `promote_end_date` int(11) unsigned NOT NULL DEFAULT '0', `warn_number` tinyint(3) unsigned NOT NULL DEFAULT '1', `keywords` varchar(255) NOT NULL DEFAULT '', `goods_brief` varchar(255) NOT NULL DEFAULT '', `goods_desc` text NOT NULL, `goods_thumb` varchar(255) NOT NULL DEFAULT '', `goods_img` varchar(255) NOT NULL DEFAULT '', `original_img` varchar(255) NOT NULL DEFAULT '', `is_real` tinyint(3) unsigned NOT NULL DEFAULT '1', `extension_code` varchar(30) NOT NULL DEFAULT '', `is_on_sale` tinyint(1) unsigned NOT NULL DEFAULT '1', `is_alone_sale` tinyint(1) unsigned NOT NULL DEFAULT '1', `is_shipping` tinyint(1) unsigned NOT NULL DEFAULT '0', `integral` int(10) unsigned NOT NULL DEFAULT '0', `add_time` int(10) unsigned NOT NULL DEFAULT '0', `sort_order` smallint(4) unsigned NOT NULL DEFAULT '100', `is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0', `is_best` tinyint(1) unsigned NOT NULL DEFAULT '0', `is_new` tinyint(1) unsigned NOT NULL DEFAULT '0', `is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0', `is_promote` tinyint(1) unsigned NOT NULL DEFAULT '0', `bonus_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0', `last_update` int(10) unsigned NOT NULL DEFAULT '0', `goods_type` smallint(5) unsigned NOT NULL DEFAULT '0', `seller_note` varchar(255) NOT NULL DEFAULT '', `give_integral` int(11) NOT NULL DEFAULT '-1', `rank_integral` int(11) NOT NULL DEFAULT '-1', `suppliers_id` smallint(5) unsigned DEFAULT NULL, `is_check` tinyint(1) unsigned DEFAULT NULL, PRIMARY KEY (`goods_id`), ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 这个是表结构。

[bojimiyabojimiya]

ecshop？

[ACMAIN_CHM]

没什么可优化的的。 如果考虑 不应该这样写 sum(if()), 你可以直接使用 union ,或者 join 比如如下方案。 select * from (select count(*) as is_hot from `icmall`.`ecs_goods` WHERE is_delete = 0 AND is_real = 1 and is_hot = 1) a, (select count(*) as is_new from `icmall`.`ecs_goods` WHERE is_delete = 0 AND is_real = 1 and is_new = 1) b, ... create index xx1 on `icmall`.`ecs_goods`(is_delete , is_real , is_hot); create index xx2 on `icmall`.`ecs_goods`(is_delete , is_real , is_new);

[ecshop528]

引用 7 楼 ACMAIN_CHM 的回复:

没什么可优化的的。 如果考虑 不应该这样写 sum(if()), 你可以直接使用 union ,或者 join 比如如下方案。 select * from (select count(*) as is_hot from `icmall`.`ecs_goods` WHERE is_delete = 0 AND is_real = 1 and is_hot ……

我现在遇到一个问题。 如果不合并。好象执行时间要30秒。我合并之后。需要20秒。 请指教。这个情况。确实很特殊

[Rotel-刘志东]

ecshop是现成的电子商务商城，具体的索引情况是怎样的。