MySQL 主表对应多表查询优化
有两张表,主表 zb_product 数据2万,属性从表 zb_product_attr 20万,根据zb_product_attr 表的属性查询 zb_product 不重复的数据,下面是我写的SQL,查询速度比较慢,请问有什么优化的解决方法?
SELECT a.* FROM zb_product a
LEFT JOIN zb_product_attr b ON a.id=b.product_id
GROUP BY a.id
ORDER BY a.id DESC
表zb_product
CREATE TABLE `zb_product` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sn` varchar(100) NOT NULL,
`name` varchar(200) NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`stock` decimal(16,2) NOT NULL DEFAULT '0.00',
`stock_unit` varchar(10) NOT NULL,
`warn_stock` float NOT NULL DEFAULT '0',
`base_price` decimal(16,2) NOT NULL DEFAULT '0.00',
`sale_price` decimal(16,2) NOT NULL DEFAULT '0.00',
`market_price` decimal(16,2) NOT NULL,
`attr_type_id` int(10) unsigned NOT NULL DEFAULT '0',
`update_time` int(10) unsigned DEFAULT NULL,
`status` smallint(5) unsigned DEFAULT '1',
`description` text,
`remark` text,
`create_time` int(10) unsigned DEFAULT NULL,
`create_account` varchar(64) NOT NULL,
`package` text,
`accessories` text,
`similar` text,
`is_birthstone` tinyint(4) DEFAULT '0',
`birthstone_type` varchar(20) DEFAULT NULL,
`is_hot` tinyint(4) DEFAULT '0',
`discount` float DEFAULT '0',
`discount_desc` varchar(100) DEFAULT NULL,
`discount_starttime` int(11) DEFAULT NULL,
`discount_endtime` int(11) DEFAULT NULL,
`is_online` tinyint(4) DEFAULT '0',
`merchant_id` int(11) NOT NULL,
`picture_url` varchar(255) DEFAULT NULL,
`thumb_picture_url` varchar(255) DEFAULT NULL,
`exchange_points` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=375440 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
表zb_product_attr
CREATE TABLE `zb_product_attr` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(10) unsigned NOT NULL DEFAULT '0',
`attr_id` int(10) unsigned NOT NULL DEFAULT '0',
`attr_value` varchar(255) NOT NULL,
`attr_price` float NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `IX_zb_product_attr` (`product_id`,`attr_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=36812 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;