How to Optimize these sql?

[Gucci Koo]

this is the first sql, it's ok, it use index, no temp table, no filesort

EXPLAIN    SELECT Id, ServerId, Level, COUNT(account.id) as Friends 
    FROM account JOIN friend ON account.Id = friend.AccountId 
    WHERE account.Id = 40 OR account.Id in (select FriendId from friend where AccountId=40)
    GROUP BY account.Id;

+----+--------------------+---------+--------+---------------+-----------+---------+---------------------+------+-------------+
| id | select_type        | table   | type   | possible_keys | key       | key_len | ref                 | rows | Extra       |
+----+--------------------+---------+--------+---------------+-----------+---------+---------------------+------+-------------+
|  1 | PRIMARY            | account | index  | PRIMARY       | PRIMARY   | 4       | NULL                |   49 | Using where |
|  1 | PRIMARY            | friend  | ref    | AccountId     | AccountId | 4       | dev_8001.account.Id |    1 | Using index |
|  2 | DEPENDENT SUBQUERY | friend  | eq_ref | AccountId     | AccountId | 8       | const,func          |    1 | Using index |
+----+--------------------+---------+--------+---------------+-----------+---------+---------------------+------+-------------+

this sql is simpler than previous one, there's no group by and count(),
but it scan the whole table, why?
i've tried to use "force index(PRIMARY)", but it doesn't work.
who could help me avoid scan the `account` table;

EXPLAIN    SELECT Id, ServerId, Level, Experience FROM account 
    WHERE Id = 40 OR Id in (select FriendId from friend where AccountId=40); 
+----+--------------------+---------+--------+---------------+-----------+---------+------------+------+-------------+
| id | select_type        | table   | type   | possible_keys | key       | key_len | ref        | rows | Extra       |
+----+--------------------+---------+--------+---------------+-----------+---------+------------+------+-------------+
|  1 | PRIMARY            | account | ALL    | PRIMARY       | NULL      | NULL    | NULL       |   49 | Using where |
|  2 | DEPENDENT SUBQUERY | friend  | eq_ref | AccountId     | AccountId | 8       | const,func |    1 | Using index |
+----+--------------------+---------+--------+---------------+-----------+---------+------------+------+-------------+

Edited 1 time(s). Last edit at 12/10/2010 04:02AM by Gucci Koo.