SQL 检查用户关系是否存在

发布于 2024-10-14 19:27:35 字数 468 浏览 1 评论 0原文

我设置了一个如下所示的用户关系表。

user_relationshiplation_id

2
requesting_user_idrequested_user_idrelationship_typerelationship_status
和
1
。

现在假设我想检查一个关系，看看两个用户之间是否存在关系，在这个例子中，ID 为 2.我可以这样做：

SELECT * 
  FROM user_relationship 
 WHERE (requesting_user_id='1' AND  requested_user_id='2') 
    || (requesting_user_id='2' AND  requested_user_id='1')

但我想知道他们是否是更好更快的方法？

原文

I have a user relationship table set up that looks like this.

user_relationship

relationship_id
requesting_user_id
requested_user_id
relationship_type
relationship_status

Now say I want to check a relationship to see if it exist between two users who lets say for this example are IDs 1 & 2. I could do a:

SELECT * 
  FROM user_relationship 
 WHERE (requesting_user_id='1' AND  requested_user_id='2') 
    || (requesting_user_id='2' AND  requested_user_id='1')

But I am wondering if their is a better faster way to do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一束光，穿透我孤独的魂 2024-10-21 19:27:35

手术室的表现是出了名的差。您可以尝试使用 UNION：

SELECT a.* 
  FROM USER_RELATIONSHIP a
 WHERE a.requesting_user_id = '1' 
   AND a.requested_user_id = '2'
UNION
SELECT b.* 
  FROM USER_RELATIONSHIP b
 WHERE b.requesting_user_id = '2' 
   AND b.requested_user_id = '1'

UNION 将删除重复项； UNION ALL 不会（而且速度更快）。

如果有任何返回的列您不使用——它们不应该出现在查询中。

索引应该是：

requesting_user_idrequested_user_id
。

...单独或单个复合索引，但您必须进行测试才能知道哪种效果最好

ORs are notoriously bad performers. You could try a UNION instead:

SELECT a.* 
  FROM USER_RELATIONSHIP a
 WHERE a.requesting_user_id = '1' 
   AND a.requested_user_id = '2'
UNION
SELECT b.* 
  FROM USER_RELATIONSHIP b
 WHERE b.requesting_user_id = '2' 
   AND b.requested_user_id = '1'

UNION will remove duplicates; UNION ALL will not (and is faster for it).

If there are any columns coming back that you don't use -- they shouldn't be in the query.

Indexing should be on:

requesting_user_id
requested_user_id

...either separately or a single composite index but you'll have to test to know which works best.

回复收藏 0 原文

烧了回忆取暖 2024-10-21 19:27:35

如果您假设用户无法向自己发送请求，那么这应该是等效的：

SELECT *
FROM user_relationship
WHERE requesting_user_id IN(1, 2) AND requested_user_id IN(1, 2);

我怀疑这实际上会更快，但至少更容易阅读（在我看来）。

确保您有一个涵盖 requesting_user_id 和 requests_user_id 的索引 - 如果您经常执行此查询，那么这样的索引绝对是必需的。

If you assume that a user can not send a request to themselves, this should be equivalent:

SELECT *
FROM user_relationship
WHERE requesting_user_id IN(1, 2) AND requested_user_id IN(1, 2);

I doubt this would actually be any faster, but it is at least easier to read (in my opinion).

Make sure you have a single index that covers both requesting_user_id and requested_user_id - if you are doing this query often, such an index is definitely a requirement.

回复收藏 0 原文

~没有更多了~