我希望你能帮我优化这个查询。
SELECT carrier,
SUM(views) AS views,
SUM(views-1) AS repeated,
SUM(views >= 1) AS unique_views,
COUNT(1) AS total
FROM stats s1
WHERE id_link = 39
AND EXISTS (
SELECT *
FROM stats s2
where id_link = 39
AND s2.carrier = s1.carrier
LIMIT 1, 1 )
GROUP BY carrier
HAVING COUNT(1) >= 1
LIMIT 1,1
UNION
SELECT 'TOTAL' AS carrier,
SUM(views) AS views,
SUM(views-1) AS repeated,
SUM(views >= 1) AS unique_views,
COUNT(1) AS total
FROM stats s4
WHERE id_link = 39
AND EXISTS (
SELECT *
FROM stats s3
where s3.carrier = s4.carrier
LIMIT 1, 1 )
数据库:
CREATE TABLE `stats` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_link` int(11) NOT NULL,
`country` varchar(100) NOT NULL,
`ip` varchar(100) NOT NULL,
`views` varchar(1000) NOT NULL,
`asn` varchar(1000) NOT NULL,
`carrier` varchar(1000) NOT NULL,
`type` varchar(1000) NOT NULL,
`device` varchar(1000) NOT NULL,
`browser` varchar(1000) NOT NULL,
`fecha` datetime NOT NULL,
`referrer` varchar(2000) NOT NULL,
PRIMARY KEY (`id`),
KEY `id_link` (`id_link`),
CONSTRAINT `stats_ibfk_1` FOREIGN KEY (`id_link`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=614606 DEFAULT CHARSET=utf8
我正在创建一个网络流量跟踪器。
我有一张叫做“统计”的桌子,放在那里;视图、ASN、IP、浏览器、设备等,其中包括“运营商”,即互联网服务提供商
我想看看每个运营商有多少次访问,账户重复了不止一次,有多少次独特的访问。
我的代码已经实现了我想要的功能,但它只在记录很少的情况下工作,现在我有超过10万条记录,服务器崩溃,它无法加载查询,我甚至必须重新启动apache。
这里我只留下一个说明性的图片
有没有办法加快这个查询的速度?
提前感谢您的评论和帮助。