MySQL Forums
Forum List  »  Performance

Re: bad Join Performance
Posted by: leo mueller
Date: November 01, 2011 03:33AM

Hi Jørgen,

thanks for your reply.

for drill down by one tag:
EXPLAIN SELECT name,count(*),tags.tag_id AS 'id' 
FROM base 
INNER JOIN tags ON base.tag_id = tags.tag_id 
INNER JOIN base REV0 ON REV0.`hash`=base.`hash` 	
WHERE 
usr_id = 4
AND REV0.tag_id=75
GROUP BY tags.tag_id;

+----+-------------+-------+--------+----------------------------+------------+---------+------------------------+---------+----------------------------------------------+
| id | select_type | table | type   | possible_keys              | key        | key_len | ref                    | rows    | Extra                                        |
+----+-------------+-------+--------+----------------------------+------------+---------+------------------------+---------+----------------------------------------------+
| 1  | SIMPLE      | REV0  | ref    | tag_id,hash_value,tag_hash | tag_hash   | 4       | const                  | 3036832 | Using index; Using temporary; Using filesort |
| 1  | SIMPLE      | base  | ref    | tag_id,hash_value,tag_hash | hash_value | 96      | gt.REV0.hash   | 1       |                                              |
| 1  | SIMPLE      | tags  | eq_ref | PRIMARY,usr_id             | PRIMARY    | 4       | gt.base.tag_id | 1       | Using where                                  |
+----+-------------+-------+--------+----------------------------+------------+---------+------------------------+---------+----------------------------------------------+


and a deeper drill down by second tag:
EXPLAIN SELECT name,count(*),tags.tag_id AS 'id' 
FROM base 
INNER JOIN tags ON base.tag_id = tags.tag_id 
INNER JOIN base REV0 ON REV0.`hash`=base.`hash` 
INNER JOIN base REV1 ON REV1.`hash`=base.`hash`	
WHERE 
usr_id = 4
AND REV0.tag_id=75
AND REV1.tag_id=52
GROUP BY tags.tag_id;

+----+-------------+-------+--------+----------------------------+------------+---------+------------------------+---------+----------------------------------------------+
| id | select_type | table | type   | possible_keys              | key        | key_len | ref                    | rows    | Extra                                        |
+----+-------------+-------+--------+----------------------------+------------+---------+------------------------+---------+----------------------------------------------+
| 1  | SIMPLE      | REV0  | ref    | tag_id,hash_value,tag_hash | tag_hash   | 4       | const                  | 3036832 | Using index; Using temporary; Using filesort |
| 1  | SIMPLE      | REV1  | ref    | tag_id,hash_value,tag_hash | hash_value | 96      | gt.REV0.hash   | 1       | Using where                                  |
| 1  | SIMPLE      | base  | ref    | tag_id,hash_value,tag_hash | hash_value | 96      | gt.REV0.hash   | 1       |                                              |
| 1  | SIMPLE      | tags  | eq_ref | PRIMARY,usr_id             | PRIMARY    | 4       | gt.base.tag_id | 1       | Using where                                  |
+----+-------------+-------+--------+----------------------------+------------+---------+------------------------+---------+----------------------------------------------+

the performance get better on each drill down level, properly because less records are relevant. But the first and second drill downs for tags used by 1.000.000+ Records takes for ever to process.



Edited 1 time(s). Last edit at 11/01/2011 03:33AM by leo mueller.

Options: ReplyQuote


Subject
Views
Written By
Posted
3723
October 31, 2011 12:21AM
1083
October 31, 2011 12:41PM
934
November 01, 2011 12:12AM
1162
November 01, 2011 12:26AM
Re: bad Join Performance
946
November 01, 2011 03:33AM
1047
November 01, 2011 08:29AM
964
November 02, 2011 08:21AM
883
November 03, 2011 12:56AM
896
November 03, 2011 12:24AM
1029
November 04, 2011 06:35AM
982
November 02, 2011 06:23AM
899
November 02, 2011 07:42AM


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.