SELECT m.* from message m
INNER JOIN `case` c on c.EbayAccountId=m.EbayAccountId
WHERE m.EbayAccountId is not NULL and m.id>0
GROUP BY m.EbayAccountId
LIMIT 10;1 SIMPLE m index PRIMARY,IDX_Id_Created,IDX_EbayAccountId_Created IDX_EbayAccountId_Created 149 110 Using where
1 SIMPLE c ref IX_EbayAccountId_Created IX_EbayAccountId_Created 144 customerservicesystem.m.EbayAccountId 176 Using index表结构:
CREATE TABLE `message` (
`Id` char(36) NOT NULL,
`EbayId` varchar(20) NOT NULL,
`ExternalId` varchar(100) DEFAULT NULL ,
`Subject` text NOT NULL ,
`Body` longtext ,
`CustomerAccount` varchar(100) NOT NULL ,
`EbayAccountId` char(36) NOT NULL ,
`MessageTime` datetime NOT NULL ,
`EbayItemId` varchar(20) DEFAULT NULL COMMENT 'ItemNo',
`ItemId` char(36) DEFAULT NULL COMMENT '产品Id',
`ItemCode` varchar(50) DEFAULT NULL COMMENT '产品名称SKU/SPU',
`ItemType` int(11) DEFAULT NULL COMMENT '产品类型',
`EbayMessageType` varchar(50) NOT NULL,
`EbayQuestionType` varchar(50) NOT NULL,
`IsReceived` tinyint(4) NOT NULL COMMENT '区分消息来源:true:表示买家发送过来接收的消息,fase表示回复的消息',
`HasReplied` tinyint(4) NOT NULL COMMENT '是否已回复',
`HasRead` tinyint(4) NOT NULL COMMENT '是否已读',
`OwnerId` char(36) DEFAULT NULL COMMENT '处理客服ID',
`OwnerName` varchar(50) DEFAULT NULL COMMENT '处理客服名称',
`Level` int(11) NOT NULL COMMENT '级别: Normal:标准,Urgent:紧急,NoNeedReply:不必回复',
`IsAfterSale` tinyint(4) NOT NULL COMMENT '是否售后',
`Created` datetime NOT NULL COMMENT '记录生成日期',
`BodyGzip` longblob,
PRIMARY KEY (`Id`),
KEY `IDX_EBAYID` (`EbayId`),
KEY `IDX_OWNERID_ISRECEIVED` (`OwnerId`,`IsReceived`),
KEY `IX_CustomerAccount` (`CustomerAccount`),
KEY `IX_EbayItemId` (`EbayItemId`),
KEY `IDX_Id_Created` (`Id`,`Created`),
KEY `IX_ExternalId` (`ExternalId`),
KEY `IDX_Created` (`Created`),
KEY `IDX_EbayAccountId_Created` (`EbayAccountId`,`Created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INNER JOIN `case` c on c.EbayAccountId=m.EbayAccountId
WHERE m.EbayAccountId is not NULL and m.id>0
GROUP BY m.EbayAccountId
LIMIT 10;1 SIMPLE m index PRIMARY,IDX_Id_Created,IDX_EbayAccountId_Created IDX_EbayAccountId_Created 149 110 Using where
1 SIMPLE c ref IX_EbayAccountId_Created IX_EbayAccountId_Created 144 customerservicesystem.m.EbayAccountId 176 Using index表结构:
CREATE TABLE `message` (
`Id` char(36) NOT NULL,
`EbayId` varchar(20) NOT NULL,
`ExternalId` varchar(100) DEFAULT NULL ,
`Subject` text NOT NULL ,
`Body` longtext ,
`CustomerAccount` varchar(100) NOT NULL ,
`EbayAccountId` char(36) NOT NULL ,
`MessageTime` datetime NOT NULL ,
`EbayItemId` varchar(20) DEFAULT NULL COMMENT 'ItemNo',
`ItemId` char(36) DEFAULT NULL COMMENT '产品Id',
`ItemCode` varchar(50) DEFAULT NULL COMMENT '产品名称SKU/SPU',
`ItemType` int(11) DEFAULT NULL COMMENT '产品类型',
`EbayMessageType` varchar(50) NOT NULL,
`EbayQuestionType` varchar(50) NOT NULL,
`IsReceived` tinyint(4) NOT NULL COMMENT '区分消息来源:true:表示买家发送过来接收的消息,fase表示回复的消息',
`HasReplied` tinyint(4) NOT NULL COMMENT '是否已回复',
`HasRead` tinyint(4) NOT NULL COMMENT '是否已读',
`OwnerId` char(36) DEFAULT NULL COMMENT '处理客服ID',
`OwnerName` varchar(50) DEFAULT NULL COMMENT '处理客服名称',
`Level` int(11) NOT NULL COMMENT '级别: Normal:标准,Urgent:紧急,NoNeedReply:不必回复',
`IsAfterSale` tinyint(4) NOT NULL COMMENT '是否售后',
`Created` datetime NOT NULL COMMENT '记录生成日期',
`BodyGzip` longblob,
PRIMARY KEY (`Id`),
KEY `IDX_EBAYID` (`EbayId`),
KEY `IDX_OWNERID_ISRECEIVED` (`OwnerId`,`IsReceived`),
KEY `IX_CustomerAccount` (`CustomerAccount`),
KEY `IX_EbayItemId` (`EbayItemId`),
KEY `IDX_Id_Created` (`Id`,`Created`),
KEY `IX_ExternalId` (`ExternalId`),
KEY `IDX_Created` (`Created`),
KEY `IDX_EbayAccountId_Created` (`EbayAccountId`,`Created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
解决方案 »
- mysql 儲存文件的疑問
- MySQL中如何删除空行空白行
- MySQL菜鸟问题···
- 关于mysql的一个问题,谢谢
- 海量数据的删除
- solaris上的postgresql去哪里讨论?
- 各位大侠我问一个关于Postgresql数据库导入到ORCALE里面去的问题
- PGSql中如何设定修改用户密码?在PGADMIN中修改无效
- 表列存在,update的时候显示“Unknown column '' in 'field list'”
- 有没有能把简体网页转换成繁体网页的工具(php中的)win2000+iis5+mysql
- PHP连接mysql时出现Fatal error: Call to undefined function mysql_connect()
- Navicat for MySQL中导入CSV文件出现问题
虽然有 limit 10,但这个作用于 group by 的结果,所以如果数据量非常大的话,肯定是要处理长时间的