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

mysql performance distinct inner-join explain
defuz picture defuz · Aug 30, 2012 · Viewed 7.7k times · Source

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!

Answer

DadViegas picture DadViegas · Aug 30, 2012

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

Related questions

sql group by versus distinct

Why would someone use a group by versus distinct when there are no aggregations done in the query? Also, does someone know the group by versus distinct performance considerations in MySQL and SQL Server. I'm guessing that SQL Server has …

Read More
MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More