Re: How to Optimize these sql?

[Rick James]

You have:

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; 
SELECT  Id, ServerId, Level, Experience
    FROM  account
    WHERE  Id = 40
      OR  Id in (
        SELECT  FriendId
            from  friend
            where  AccountId=40);

Two things are especially inefficient:
* OR
* IN ( SELECT ... )

# IN --> JOIN (but omitting the OR):
SELECT  a.Id, a.ServerId, a.Level, a.Experience
    FROM  account AS a
    JOIN  friend AS f  ON f.FriendId = a.Id AND AccoutnId=40

# OR --> UNION (but ignoring the 'IN' inefficiency):
SELECT  Id, ServerId, Level, Experience
    FROM  account
    WHERE  Id = 40
UNION DISTINCT  -- 'ALL' would be more efficient, but would it get dups?
SELECT  Id, ServerId, Level, Experience
    FROM  account
    WHERE  Id in (
        SELECT  FriendId
            from  friend
            where  AccountId=40);

I'll leave it to you to combine the principles.