我需要优化以下查询:
SELECT a.*
FROM Activity AS a
JOIN users_following AS f1
ON f1.userId = a.originatorId
AND f1.followerId = 1
ORDER
BY a.time DESC
LIMIT 10
其思想是获取由某些用户(在本例中为用户1)所跟随的用户发起的所有activity,并按时间排序。这个查询写得非常慢(~5s),但如果a)省略连接或b)省略order by子句,它会非常快。
我尝试过的事情:
其中...在
中,而不是内部联接
下面是CREATE TABLE和EXPLAIN定义。
CREATE TABLE `Activity` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time` int(11) NOT NULL,
`userId` int(11) NOT NULL,
`voteId` int(11) DEFAULT NULL,
`commentId` int(11) DEFAULT NULL,
`achievementId` int(11) DEFAULT NULL,
`challengeId` int(11) DEFAULT NULL,
`followerId` int(11) DEFAULT NULL,
`acknowledged` int(11) NOT NULL DEFAULT '0',
`type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`isPrivate` int(11) NOT NULL DEFAULT '0',
`portalId` int(11) DEFAULT NULL,
`postId` int(11) DEFAULT NULL,
`portalMemberId` int(11) DEFAULT NULL,
`originatorId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_55026B0C1CC880D8` (`portalMemberId`),
KEY `IDX_55026B0C1D79C36A` (`challengeId`),
KEY `IDX_55026B0CE7A069D0` (`achievementId`),
KEY `IDX_55026B0CB6FEC0EE` (`voteId`),
KEY `IDX_55026B0C6690C3F5` (`commentId`),
KEY `IDX_55026B0C64B64DCC` (`userId`),
KEY `IDX_55026B0CF542AA03` (`followerId`),
KEY `IDX_55026B0C57076B1F` (`portalId`),
KEY `IDX_55026B0CE094D20D` (`postId`),
KEY `IDX_55026B0C162E014D` (`originatorId`),
KEY `activity_time_idx` (`time`),
KEY `activity_filter_idx` (`type`,`originatorId`,`userId`,`isPrivate`),
KEY `acknowledged_idx` (`acknowledged`),
KEY `idx1` (`time`,`originatorId`),
KEY `idx2` (`originatorId`,`userId`,`postId`,`challengeId`,`commentId`,`time`),
CONSTRAINT `FK_55026B0C162E014D` FOREIGN KEY (`originatorId`) REFERENCES `ProseUser` (`id`),
CONSTRAINT `FK_55026B0C1CC880D8` FOREIGN KEY (`portalMemberId`) REFERENCES `PortalMember` (`id`),
CONSTRAINT `FK_55026B0C1D79C36A` FOREIGN KEY (`challengeId`) REFERENCES `Challenge` (`id`),
CONSTRAINT `FK_55026B0C57076B1F` FOREIGN KEY (`portalId`) REFERENCES `Portal` (`id`),
CONSTRAINT `FK_55026B0C64B64DCC` FOREIGN KEY (`userId`) REFERENCES `ProseUser` (`id`),
CONSTRAINT `FK_55026B0C6690C3F5` FOREIGN KEY (`commentId`) REFERENCES `Comment` (`id`),
CONSTRAINT `FK_55026B0CB6FEC0EE` FOREIGN KEY (`voteId`) REFERENCES `Vote` (`id`),
CONSTRAINT `FK_55026B0CE094D20D` FOREIGN KEY (`postId`) REFERENCES `Post` (`id`),
CONSTRAINT `FK_55026B0CE7A069D0` FOREIGN KEY (`achievementId`) REFERENCES `UserAchievement` (`id`),
CONSTRAINT `FK_55026B0CF542AA03` FOREIGN KEY (`followerId`) REFERENCES `ProseUser` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4097200 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
CREATE TABLE `users_following` (
`userId` int(11) NOT NULL,
`followerId` int(11) NOT NULL,
PRIMARY KEY (`userId`,`followerId`),
KEY `IDX_17C2F70264B64DCC` (`userId`),
KEY `IDX_17C2F702F542AA03` (`followerId`),
KEY `idx1` (`userId`,`followerId`),
KEY `idx2` (`followerId`,`userId`),
CONSTRAINT `FK_17C2F70264B64DCC` FOREIGN KEY (`userId`) REFERENCES `ProseUser` (`id`),
CONSTRAINT `FK_17C2F702F542AA03` FOREIGN KEY (`followerId`) REFERENCES `ProseUser` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
解释
+----+-------------+-------+------------+------+-------------------------------------------------------------+----------------------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------------------------------------------+----------------------+---------+---------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | f1 | NULL | ref | PRIMARY,IDX_17C2F70264B64DCC,IDX_17C2F702F542AA03,idx1,idx2 | IDX_17C2F702F542AA03 | 4 | const | 145 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | NULL | ref | IDX_55026B0C162E014D,idx2 | IDX_55026B0C162E014D | 5 | prose_2_24_2021.f1.userId | 38 | 100.00 | NULL |
+----+-------------+-------+------------+------+-------------------------------------------------------------+----------------------+---------+---------------------------+------+----------+----------------------------------------------+
让我们举个例子:用户#1跟随用户#2和#3。下面是用户订购的活动,然后是时间:
+------+-------+ | user | time | +------+-------+ | 1 | 10:00 | | 1 | 11:00 | | 2 | 10:00 | | 2 | 12:00 | | 3 | 09:00 | | 3 | 13:00 | | 4 | 10:00 | +------+-------+
我们可以快速找到跟随的用户活动,但得到的是无序的次数:
+------+-------+ | user | time | +------+-------+ | 2 | 10:00 | | 2 | 12:00 | | 3 | 09:00 | | 3 | 13:00 | +------+-------+
这意味着我们必须对它们进行排序,以便得到排序的前n个。如果这些活动不是四千个,而是几千个或几百万个,那就需要很长的时间。
另一方面,如果数据是按时间递减顺序提供的,则用户:
+------+-------+ | user | time | +------+-------+ | 3 | 13:00 | | 2 | 12:00 | | 1 | 11:00 | | 1 | 10:00 | | 2 | 10:00 | | 4 | 10:00 | | 3 | 09:00 | +------+-------+
我们必须按顺序读取整个数据,直到找到n个最重要的活动。无需进一步订购。如果我们幸运的话,前n行是匹配的,就这样。如果我们不走运,我们会阅读整个表(或索引)。
所以,不能保证能很快得到这个。第一种方法获取数据的速度很快,但排序可能需要很长时间。第二种方法不需要排序,但读取可能需要很长时间。
我更喜欢第二种方法,但这完全取决于数据。用户多吗?是大多数人都跟在用户#1后面,还是只有少数人?活动多吗?还是每个用户的活动很少?...无论如何,我会提供以下索引:
create index idx1 on activity (time desc, originatorid);
由于索引只是提供给DBMS的一种服务,因此,如果DBMS希望遵循另一条路由,我们也可以提供另一条索引:
create index idx2 on activity (originatorid, time desc);
这可能是我编写查询的方式:
SELECT a.*
FROM activity AS a
WHERE EXISTS
(
SELECT NULL
FROM users_following AS f1
WHERE f1.userId = a.originatorId
AND f1.followerId = 1
)
ORDER BY a.time DESC
LIMIT 10;
问题内容: 我有一个关于MYSQL中的选择查询的问题 我有两个不同的表,我想获得一定的结果 我使用了COUNT方法,该方法只给我结果(> = 1) 但实际上,我想使用包含零的所有计数怎么做? 我的查询是: 第一张桌子:Content_hits 第二张表:Content_tag 结果但不完整 例如:tag_id = 1的Subsrciber6应该具有count(*)= 0 问题答案: 现在,您已经进
如果我通过“.ASC”删除组的第二部分,但当我这样做时,数据是不正确的。它为什么要这样做,我如何修复它,并且仍然按照表1分组,除了表3之外,还要先按表1分组。 先进的谢谢! 我有时间接受完整的查询,并将表拉到一个通用表单,以便在没有客户端数据的情况下发布。我能够将模式添加到sqlfiddle中,但如果没有我使用的数据,结果可能会有所不同,而且由于字符的限制,我甚至无法将表前的100行(共7行)放入
问题内容: 我看到了许多与此主题有关的主题,但在了解如何做到这一点上一直没有成功。 例如,如果我有此表: 并且我只想显示“一个”类中的X个随机行,我该怎么做? 注意 :这是一张大桌子,所以我不想使用。 问题答案: 如您所知,大多数人推荐的解决方案无法扩展到大型表。 我在《SQL反模式:避免数据库编程的陷阱》一书中介绍了该解决方案和其他解决方案。 如果要使用PHP进行此操作,则可以执行以下操作(未测
1)使用的第一个查询...大约用了23秒 目前我修改了查询..这需要大约9秒 我不确定要做的表演是什么?我希望这个查询是快速的..我尝试索引rid和id,但这仍然使查询变得更糟。 下面是表格的详细信息 mza_movie_upload MZA_Movie_Statics
本文向大家介绍使用MySQL子查询选择年龄最大的所有用户?,包括了使用MySQL子查询选择年龄最大的所有用户?的使用技巧和注意事项,需要的朋友参考一下 让我们首先创建一个表- 使用插入命令在表中插入一些记录- 使用select语句显示表中的所有记录- 这将产生以下输出- 以下是查询以选择具有最大年龄值的所有用户- 这将产生以下输出-
本文向大家介绍Mysql使用索引实现查询优化,包括了Mysql使用索引实现查询优化的使用技巧和注意事项,需要的朋友参考一下 索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的。 1.索引的优点 假设你拥有三个未索引的表t1、t2和t3,每个表都分