如何确定哪个更有效:DISTINCT或WHERE EXISTS? [英] How to determine what is more effective: DISTINCT or WHERE EXISTS?

问题描述

例如，我有3个表:user，group和permission，并且它们之间有两个很多很多的关系:user_groups和group_permissions.

For example, I have 3 tables: user, group and permission, and two many2many relationships between them: user_groups and group_permissions.

我需要选择给定用户的所有权限，而无需重复.每次遇到类似问题时，我都无法确定哪个版本的查询更好:

I need to select all permissions of given user, without repeats. Every time I encounter a similar problem, I can not determine which version of a query better:

SELECT permisson_id FROM group_permission WHERE EXISTS(
    SELECT 1 FROM user_groups 
        WHERE user_groups.user_id = 42 
          AND user_groups.group_id = group_permission.group_id
)

SELECT DISTINCT permisson_id FROM group_permission
    INNER JOIN user_groups ON user_groups.user_id = 42 
           AND user_groups.group_id = group_permission.group_id 

我有足够的经验根据解释得出结论.第一个查询具有子查询，但是我的经验表明，第一个查询速度更快.可能是由于结果中过滤了大量权限.

I have enough experience to make conclusions based on explain. The first query have subquery, but my experiences have shown that the first query is faster. Perhaps because of the large number of filtered permissions in result.

在这种情况下您会怎么做?为什么? 谢谢！

What would you do in this situation? Why? Thanks!

推荐答案

使用EXISTS而不是DISTINCT

Use EXISTS Rather than DISTINCT

您可以使用DISTINCT禁止显示重复的行；您使用EXISTS检查子查询返回的行是否存在.只要有可能，就应该使用EXISTS而不是DISTINCT，因为DISTINCT在抑制重复行之前会对检索到的行进行排序.

You can suppress the display of duplicate rows using DISTINCT; you use EXISTS to check for the existence of rows returned by a subquery. Whenever possible, you should use EXISTS rather than DISTINCT because DISTINCT sorts the retrieved rows before suppressing the duplicate rows.

在您的情况下，应该有很多重复的数据，因此存在的速度应该更快.

in your case there whould be many duplicated data so the exists should be faster.

通过 http://my.safaribooksonline.com /book/-//9780072229813/high-performance-sql-tuning/ch16lev1sec10

这篇关于如何确定哪个更有效:DISTINCT或WHERE EXISTS?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！