Hi,
There's no wonder this has a high cost. According to this explain...:
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 |
+----+-------------+-------+--------+----------------------------+------------+---------+------------------------+---------+----------------------------------------------+
...MySQL expects to find ~3M rows in base with tagid 75. Then you join those 3M rows with about 67000 rows in base (67000 because there are ~8.5M rows in base and only 128 different tags). That's a high number of resulting rows (2*10^11). I'm sure you see the problem.
Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com
Edited 1 time(s). Last edit at 11/02/2011 11:06PM by Jorgen Loland.