MySQL Forums
Forum List  »  Optimizer & Parser

How to Optimize these sql?
Posted by: Gucci Koo
Date: December 10, 2010 04:02AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to Optimize these sql?
2086
December 10, 2010 04:02AM
1004
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.