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.