提高我的 SQL 的效率 [英] Improving Efficiency of my SQL

问题描述

我有一个包含 LIKES(likeID、userID、objectID、likeDate)的 MySQL 表，我希望能够计算出在相关用户之后获得的所有喜欢".

I have a MySQL table of LIKES (likeID,userID,objectID,likeDate) and I would like to be able to count all the 'likes' that have been made after the user in question.

通常我会得到日期:

SELECT likeDate FROM LIKES WHERE userID = <logged in user's ID>

然后像这样查找所有日期并计算返回的行数(或使用 mysql COUNT):

and then find all dates and count the row returned (or use mysql COUNT) like this:

SELECT * FROM LIKES WHERE likeDate > <given date>

但是，我确信有一种方法可以在一个查询中执行此操作，而不是对数据库进行两次调用.有人可以帮忙吗?

However, I'm sure there is a way to do this in one query rather than making two calls to the database. Can anyone help?

谢谢

推荐答案

将第一个查询的结果直接送入第二个:

Feed the result of the first query directly into the second one:

SELECT COUNT(*)
FROM LIKES
WHERE likeDate > (
    SELECT max(likeDate)
    FROM LIKES
    WHERE userID = <logged in user's ID>
)

但是请注意，您需要在第一个查询中添加 max() 的使用.

However note that you need to add the use of max() in your first query.

此查询应该是获得答案的最快方式.为确保最佳性能，请在 userID 和 likeDate 上添加索引:

This query should be the fastest possible way to get your answer. To ensure maximum performance, add indexes on both userID and likeDate:

create index likes_userId on likes(userID);
create index likes_likeDate on likes(likeDate);

这篇关于提高我的 SQL 的效率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！