40w的数据用这查询,花了15秒还加了LIMIT 0 , 2
。。数据库是MYSQL有索引能优化吗。。
其它数据库有索引能优化也能建议一下。。
显示行 0 - 1 (2 总计, 查询花费 15.1754 秒)
SELECT A.sname, (SELECT COUNT( * )
FROM (SELECT sname, COUNT( * )
FROM `data_source` AS B1
WHERE 1
GROUP BY visitorid
HAVING COUNT( B1.`visitorid` ) =1
) AS B2
WHERE sname = A.sname
) AS T1COUNT
FROM `data_source` AS A
WHERE `stype`
IN ( 1, 2 )
GROUP BY `sname`
LIMIT 0 , 2
。。数据库是MYSQL有索引能优化吗。。
其它数据库有索引能优化也能建议一下。。
显示行 0 - 1 (2 总计, 查询花费 15.1754 秒)
SELECT A.sname, (SELECT COUNT( * )
FROM (SELECT sname, COUNT( * )
FROM `data_source` AS B1
WHERE 1
GROUP BY visitorid
HAVING COUNT( B1.`visitorid` ) =1
) AS B2
WHERE sname = A.sname
) AS T1COUNT
FROM `data_source` AS A
WHERE `stype`
IN ( 1, 2 )
GROUP BY `sname`
LIMIT 0 , 2
id int(11) 否 auto_increment
visitorid int(11) 否 0
memberid int(11) 否 0
reference varchar(255) latin1_swedish_ci 否
domain varchar(255) latin1_swedish_ci 否
path varchar(255) latin1_swedish_ci 否
ip int(4) UNSIGNED 否 0
stype char(1) latin1_swedish_ci 否
sname varchar(255) latin1_swedish_ci 否
scontent varchar(255) latin1_swedish_ci 否
smediatype varchar(255) latin1_swedish_ci 否
sadid int(8) 否 0
cityid int(4) 否 0
browser varchar(255) latin1_swedish_ci 否
表结构是什么?
键名 类型 基数 操作 字段
PRIMARY PRIMARY 448939 id
index_sname INDEX 87 sname
index_stype INDEX 3 stype
fulltext_sname FULLTEXT 1 sname
T1COUNT=在按visitorid分组统计总行数等1行数
返回sname T1COUNT
abc 5
cba 2
from data_source t inner join (
select sanme,visitorid,count(*)
from data_source
group by sanme,visitorid
having count(*)=1
) e on t.sanme=e.sanme
where t.stype=1 or t.stype=2
group by t.sanme
返回不了超时了。。
select e.sanme,count(e.visitorid)
from (
select sanme,visitorid,stype
from data_source
group by sanme,visitorid
having count(*)=1
) e where stype=1 or stype=2
group by sanme应该和 liuyann的结果集是一样的。
楼主的sql语句不得看,就不看了。