yuantel_个人页

回47楼yuantel的帖子 对第一个语句，我尝试去掉Left Join，仍然还有此问题，所以比较困惑 mysql> explain SELECT b.brand_id, b.brand_name, COUNT(*) AS goods_num FROM `hcsyw`.`ecs_brand`AS b, `hcsyw`.`ecs_goods` AS g   WHERE g.brand_id = b.brand_id AND g.cat_id='1671'  AND b.is_show = 1  AND g.is_on_sale = 1 AND g.is_alone_sale = 1 AND g.is_delete = 0 GROUP BY b.brand_id HAVING goods_num > 0 ORDER BY b.sort_order, b.brand_id ASC; +----+-------------+-------+--------+-------------------------------------------------------+---------+---------+------------------+------+----------------------------------------------+ | id | select_type | table | type   | possible_keys                                         | key     | key_len | ref              | rows | Extra                                        | +----+-------------+-------+--------+-------------------------------------------------------+---------+---------+------------------+------+----------------------------------------------+ |  1 | SIMPLE      | g     | ref    | cat_id,brand_id,idx_catid_isdel_ison,idx_sal_sort_gid | cat_id  | 2       | const            | 1826 | Using where; Using temporary; Using filesort | |  1 | SIMPLE      | b     | eq_ref | PRIMARY,is_show                                       | PRIMARY | 2       | hcsyw.g.brand_id |    1 | Using where                                  | +----+-------------+-------+--------+-------------------------------------------------------+---------+---------+------------------+------+----------------------------------------------+ 2 rows in set (0.00 sec) ------------------------- 回49楼玄惭的帖子 mysql> show create table ecs_brand; | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | | ecs_brand | CREATE TABLE `ecs_brand` (   `brand_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,   `brand_name` varchar(60) NOT NULL DEFAULT '',   `brand_logo` varchar(80) NOT NULL DEFAULT '',   `brand_desc` text NOT NULL,   `site_url` varchar(255) NOT NULL DEFAULT '',   `sort_order` tinyint(3) unsigned NOT NULL DEFAULT '50',   `is_show` tinyint(1) unsigned NOT NULL DEFAULT '1',   `brand_name1` varchar(60) DEFAULT NULL,   `lang_flag` int(2) DEFAULT '0',   PRIMARY KEY (`brand_id`),   KEY `is_show` (`is_show`),   KEY `idx_sort` (`sort_order`) ) ENGINE=MyISAM AUTO_INCREMENT=52167 DEFAULT CHARSET=utf8 | mysql> show create table ecs_goods; | Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | | ecs_goods | 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 '1.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,   `num_iid` bigint(11) NOT NULL DEFAULT '0',   `nick` varchar(100) DEFAULT NULL,   `click_url` text,   `shop_title` varchar(180) DEFAULT NULL,   `shop_click_url` varchar(255) DEFAULT NULL,   `item_location` varchar(60) DEFAULT NULL,   `volume` int(11) DEFAULT NULL,   `commission_rate` decimal(10,2) DEFAULT NULL,   `commission` decimal(10,2) DEFAULT NULL,   `commission_num` int(11) DEFAULT NULL,   `commission_volume` decimal(10,2) DEFAULT NULL,   `post_fee` decimal(10,2) DEFAULT NULL,   `express_fee` decimal(10,2) DEFAULT NULL,   `ems_fee` decimal(10,2) DEFAULT NULL,   `seller_credit_score` varchar(120) DEFAULT NULL,   `is_tbao` tinyint(1) unsigned NOT NULL DEFAULT '0',   `procotent` varchar(40) DEFAULT NULL,   `lang_flag` int(2) DEFAULT '0',   PRIMARY KEY (`goods_id`),   KEY `goods_sn` (`goods_sn`),   KEY `cat_id` (`cat_id`),   KEY `last_update` (`last_update`),   KEY `brand_id` (`brand_id`),   KEY `goods_weight` (`goods_weight`),   KEY `promote_end_date` (`promote_end_date`),   KEY `promote_start_date` (`promote_start_date`),   KEY `goods_number` (`goods_number`),   KEY `sort_order` (`sort_order`),   KEY `idx_catid_isdel_ison` (`cat_id`,`is_delete`,`is_on_sale`),   KEY `idx_gid_bid_cid_onsal_isdel_gnum` (`goods_id`,`brand_id`,`cat_id`,`is_delete`,`is_on_sale`,`goods_name`),   KEY `idx_sal_sort_gid` (`is_on_sale`,`sort_order`,`goods_id`),   KEY `idx_gid_sort` (`goods_id`,`sort_order`),   KEY `idx_sort_gid` (`sort_order`,`goods_id`) ) ENGINE=MyISAM AUTO_INCREMENT=1029092 DEFAULT CHARSET=utf8