Which query is better

EXPLAIN EXTENDED SELECT id, name
FROM member
INNER JOIN group_assoc ON ( member.id = group_assoc.member_id
AND group_assoc.group_id =2 ) 
ORDER BY registered DESC 
LIMIT 0 , 1

Outputs:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  group_assoc ref member_id,group_id  group_id    4   const   3   100.00  Using temporary; Using filesort
1   SIMPLE  member  eq_ref  PRIMARY PRIMARY 4   source_member.group_assoc.member_id 1   100.00   

explain extended SELECT
                        id, name
                    开发者_如何学运维FROM member WHERE
                        id
                    NOT IN (
                        SELECT
                            member_id
                        FROM group_assoc WHERE group_id = 2 
                    )               
                    ORDER BY registered DESC LIMIT 0,1

Outputs:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY member  ALL NULL    NULL    NULL    NULL    2635    100.00  Using where; Using filesort
2   DEPENDENT SUBQUERY  group_assoc index_subquery  member_id,group_id  member_id   8   func,const  1   100.00  Using index; Using where

The first query I'm not so sure about, it uses a temporary table which seems like a worse idea. But I also see that it uses fewer rows than the 2nd query....

These queries return completely different resultsets: the first one returns members of group 2, the second one returns everybody who is not a member of group 2.

If you meant this:

SELECT  id, name
FROM    member
LEFT JOIN
        group_assoc
ON      member.id = group_assoc.member_id
        AND group_assoc.group_id = 2
WHERE   group_assoc.member_id IS NULL
ORDER BY
        registered DESC 
LIMIT 0, 1

, then the plans should be identical.

You may find this article interesting:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

Create an index on member.registered to get rid of both filesort and temporary.

I would say the first is better. The temporary table might not be a good idea, but a subquery isn't much better. And you will give MySQL more options to optimize the query plan with an inner join than you have with a subquery.

The subquery solution is fast as long as there are just a few rows that will be returned.

But... the first and second query don't seem to be the same, should it be that way?