How to determine what is more effective: DISTINCT or WHERE EXISTS?

Question

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!

Trapp

did you check the explain plan on both of them? – Feleciafeledy

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 permissions to be filtered. – Trapp

The second have most peformance. Subquery no is fast. – Dichloride

@Dichloride yes subqueries are to be avoided; however EXISTS instructs the server to stop looking after it finds the first match, which makes it a very fast WHERE condition. – Nexus

Answer 1

Use EXISTS Rather than 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.

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

Metalware

Exists also returns after finding the first row, so you save more than just sorting; also scanning. – Nexus