三张表:tb_statistic_stat,tb_statistic_exit,tb_developers_serviceSHOW INDEX 分别为
Non_unique:0、0、0
Key_name:PRIMARY、PRIMARY、PRIMARY
Seq_in_index:1、1、1
Column_name:uid、id、id
Collation:A、A、A
Cardinality:115755、489436、46
Index_type:BTREE、BTREE、BTREE
目前的sql语句是这样SELECT * FROM tb_statistic_stat s,
(SELECT COUNT(id) visitCount,uid FROM tb_statistic_exit WHERE 1=1 GROUP BY uid) v,
tb_developers_service d
WHERE s.uid=v.uid AND s.cpid = d.developersId AND s.appid = d.productsId limit 0,10
查询需要一分多钟 求大神指点。
表结构分别如下:
Create TableCREATE TABLE `tb_statistic_stat` (
`uid` varchar(36) NOT NULL,
`appid` varchar(255) DEFAULT NULL,
`chid` varchar(10) DEFAULT NULL,
`cpid` varchar(10) DEFAULT NULL,
`cydia` varchar(255) DEFAULT NULL,
`lang` varchar(255) DEFAULT NULL,
`operator` varchar(255) DEFAULT NULL,
`ua` varchar(255) DEFAULT NULL,
`udid` varchar(255) DEFAULT NULL,
`ver` varchar(50) DEFAULT NULL,
`version` varchar(50) DEFAULT NULL,
`macAddress` varchar(255) DEFAULT NULL,
`timeString` datetime DEFAULT NULL,
PRIMARY KEY (`uid`)
)
CREATE TABLE `tb_statistic_exit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` varchar(500) DEFAULT NULL,
`visitTime` datetime DEFAULT NULL,
`onlineTime` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
)CREATE TABLE `tb_developers_service` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`developersName` varchar(20) DEFAULT NULL,
`productsName` varchar(10) DEFAULT NULL,
`productsUrl` varchar(255) DEFAULT NULL,
`appId` int(255) DEFAULT NULL,
`developersId` varchar(10) DEFAULT NULL,
`productsId` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)
Non_unique:0、0、0
Key_name:PRIMARY、PRIMARY、PRIMARY
Seq_in_index:1、1、1
Column_name:uid、id、id
Collation:A、A、A
Cardinality:115755、489436、46
Index_type:BTREE、BTREE、BTREE
目前的sql语句是这样SELECT * FROM tb_statistic_stat s,
(SELECT COUNT(id) visitCount,uid FROM tb_statistic_exit WHERE 1=1 GROUP BY uid) v,
tb_developers_service d
WHERE s.uid=v.uid AND s.cpid = d.developersId AND s.appid = d.productsId limit 0,10
查询需要一分多钟 求大神指点。
表结构分别如下:
Create TableCREATE TABLE `tb_statistic_stat` (
`uid` varchar(36) NOT NULL,
`appid` varchar(255) DEFAULT NULL,
`chid` varchar(10) DEFAULT NULL,
`cpid` varchar(10) DEFAULT NULL,
`cydia` varchar(255) DEFAULT NULL,
`lang` varchar(255) DEFAULT NULL,
`operator` varchar(255) DEFAULT NULL,
`ua` varchar(255) DEFAULT NULL,
`udid` varchar(255) DEFAULT NULL,
`ver` varchar(50) DEFAULT NULL,
`version` varchar(50) DEFAULT NULL,
`macAddress` varchar(255) DEFAULT NULL,
`timeString` datetime DEFAULT NULL,
PRIMARY KEY (`uid`)
)
CREATE TABLE `tb_statistic_exit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` varchar(500) DEFAULT NULL,
`visitTime` datetime DEFAULT NULL,
`onlineTime` varchar(500) DEFAULT NULL,
PRIMARY KEY (`id`)
)CREATE TABLE `tb_developers_service` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`developersName` varchar(20) DEFAULT NULL,
`productsName` varchar(10) DEFAULT NULL,
`productsUrl` varchar(255) DEFAULT NULL,
`appId` int(255) DEFAULT NULL,
`developersId` varchar(10) DEFAULT NULL,
`productsId` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)
explain SELECT COUNT(id) visitCount,uid FROM tb_statistic_exit WHERE 1=1 GROUP BY uid;把结果贴出来。
show index from tb_statistic_exit;
看看结果。
create index xxx2 on tb_statistic_exit(uid);然后
SELECT * ,(SELECT COUNT(id) FROM tb_statistic_exit where uid=s.uid) as visitCount
FROM tb_statistic_stat s,tb_developers_service d
WHERE s.cpid = d.developersId AND s.appid = d.productsId limit 0,10;