MySQL Forums
Forum List  »  Optimizer & Parser

Re: How to Optimize these sql?
Posted by: Rick James
Date: December 11, 2010 02:17PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2082
December 10, 2010 04:02AM
Re: How to Optimize these sql?
1001
December 11, 2010 02:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.