我的SQL语句如下 日中三个表数据量分别为 1700 13000 34 这样查询要40秒才会出来结果(我远程连的服务器MYSQL)。 我表示压力很大。请大神给予指示,SELECT
COUNT(adverId) visit,
adverId,
statId
developersName,
productsName
FROM
tb_statistic_adver_visit v,
(SELECT
chid,
uid,
cpid
FROM
tb_statistic_stat) s,
(SELECT
developersName,
productsName,
developersId,id
FROM
tb_developers_service
WHERE
1=1
GROUP BY
developersId ) d
WHERE
1=1
AND s.uid=v.statId
AND s.cpid=d.developersId
GROUP BY
adverId
COUNT(adverId) visit,
adverId,
statId
developersName,
productsName
FROM
tb_statistic_adver_visit v,
(SELECT
chid,
uid,
cpid
FROM
tb_statistic_stat) s,
(SELECT
developersName,
productsName,
developersId,id
FROM
tb_developers_service
WHERE
1=1
GROUP BY
developersId ) d
WHERE
1=1
AND s.uid=v.statId
AND s.cpid=d.developersId
GROUP BY
adverId
贴建表及插入记录的SQL,及要求结果出来看看
TABLE:tb_statistic_adver_visit
Non_unique:0
Key_name:PRIMARY
Seq_in_index:1
Column_name:id
COLLATION:A
Cardinality:1716
Sub_part:NULL
Index_type:BTREETABLE:tb_statistic_stat
0
PRIMARY
1
uid
A
17579
NULL
BTREETABLE:tb_developers_service
0
PRIMARY
1
id
A
34
NULL
BTREE
show index from tb_statistic_adver_visit;
show index from tb_statistic_stat;
show index from tb_developers_service;
show create table tb_statistic_stat;
show create table tb_developers_service;
`id` int(11) NOT NULL auto_increment,
`statId` varchar(50) default NULL,
`adverId` varchar(50) default NULL,
`visitTime` datetime default NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `tb_statistic_stat` (
`uid` varchar(50) NOT NULL default '',
`ua` varchar(255) default NULL,
`cpid` varchar(255) default NULL,
`appid` varchar(255) default NULL,
`chid` varchar(255) default NULL,
`ver` varchar(10) default NULL,
`sdkver` varchar(10) default NULL,
`cydia` varchar(255) default NULL,
`lang` varchar(255) default NULL,
`operator` varchar(255) default NULL,
`udid` varchar(255) default NULL,
`macAddress` varchar(255) default NULL,
`timeString` datetime default NULL,
`version` varchar(255) default NULL,
PRIMARY KEY (`uid`)
)
CREATE TABLE `tb_developers_service` (
`id` int(255) NOT NULL auto_increment,
`developersName` varchar(255) default NULL,
`productsName` varchar(255) default NULL,
`productsUrl` varchar(500) default NULL,
`appId` int(255) default NULL,
`developersId` varchar(255) default NULL,
`productsId` varchar(255) default NULL,
PRIMARY KEY (`id`)
)
create index idx_v_statId on tb_statistic_adver_visit(statId);
create index idx_s_cpid on tb_statistic_stat(cpid);
create index idx_d_developersId on tb_developers_service(developersId);执行下上面的 然后查询 看效率提高了没
COUNT(adverId) visit,
adverId,
statId
developersName,
productsName
FROM
tb_statistic_adver_visit v,
tb_statistic_stat s
(SELECT
developersName,
productsName,
developersId,id
FROM
tb_developers_service
WHERE
1=1
GROUP BY
developersId ) d
WHERE
1=1
AND s.uid=v.statId
AND s.cpid=d.developersId
GROUP BY
adverId
FROM tb_developers_service
WHERE 1 = 1
GROUP BY developersId为什么加group by 啊?