MySQL Forums
Forum List  »  Optimizer & Parser

Re: how to optimize this sql?
Posted by: h h
Date: July 12, 2016 09:01PM

root@localhost:legend 10:58:18> Show Create Table legend_account_coupon\G
*************************** 1. row ***************************
Table: legend_account_coupon
Create Table: CREATE TABLE `legend_account_coupon` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`is_deleted` char(1) NOT NULL DEFAULT 'N' COMMENT '删除标记',
`gmt_create` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '修改时间',
`creator` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '创建人',
`modifier` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '修改人',
`shop_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '门店ID',
`coupon_code` varchar(45) DEFAULT NULL COMMENT '优惠劵码',
`coupon_source` tinyint(3) unsigned DEFAULT NULL COMMENT '来源:0充值1赠送',
`effective_date` datetime DEFAULT NULL COMMENT '生效时间',
`expire_date` datetime DEFAULT NULL COMMENT '失效时间',
`used_status` tinyint(3) unsigned DEFAULT '0' COMMENT '是否使用0未使用1已使用',
`account_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '账户ID',
`coupon_info_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '优惠劵ID',
`coupon_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '优惠劵类型,0:折扣卷;1:现金券;2:通用券',
`coupon_name` varchar(45) NOT NULL DEFAULT '' COMMENT '优惠劵名称',
`suite_id` int(10) unsigned DEFAULT NULL COMMENT '来源某个套餐',
`flow_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '充值流水id',
`flow_sn` varchar(50) NOT NULL DEFAULT '' COMMENT '流水号',
`operator_name` varchar(50) DEFAULT NULL COMMENT '操作人名字',
PRIMARY KEY (`id`),
KEY `idx_accid` (`account_id`),
KEY `idx_coupid` (`coupon_info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=609450 DEFAULT CHARSET=utf8 COMMENT='账户优惠劵表'
1 row in set (0.00 sec)

root@localhost:legend 10:58:24>select count(*) from legend_account_coupon\G
*************************** 1. row ***************************
count(*): 609449
1 row in set (0.43 sec)

root@localhost:legend 10:59:04>select count(distinct(coupon_info_id)) from legend_account_coupon\G
*************************** 1. row ***************************
count(distinct(coupon_info_id)): 1611
1 row in set (0.18 sec)

root@localhost:legend 10:59:21>select version();
+------------+
| version() |
+------------+
| 5.5.18-log |
+------------+
1 row in set (0.00 sec)

root@localhost:legend 10:59:48>show variables like '%pool%';
+---------------------------------+------------+
| Variable_name | Value |
+---------------------------------+------------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 5368709120 |
+---------------------------------+------------+

root@localhost:legend 11:00:09>\! free -m
total used free shared buffers cached
Mem: 7869 7668 200 0 179 1518
-/+ buffers/cache: 5970 1898
Swap: 0 0 0

Options: ReplyQuote


Subject
Views
Written By
Posted
1345
h h
July 07, 2016 07:38PM
929
July 08, 2016 10:59AM
Re: how to optimize this sql?
904
h h
July 12, 2016 09:01PM
833
July 09, 2016 11:04PM
836
h h
July 12, 2016 09:08PM
804
July 14, 2016 12:52PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.