Re: bad Join Performance

[leo mueller]

select count(distinct usr_id) from tags

Result: 2
Ive got 2 Users

select count(*) from tags

Result: 128
These 2 Users create 128 unique tags (tag_id)`s

SELECT usr_id,count(*) FROM tags Group by usr_id

Result:
1 10
2 118

User 1 create 10 tag_id´s, User 2 create 118 tag_id´s

select count(*) from base

Result: 8545650

each record contains a hash value (not uniqe) bound to a tag_id and a timestamp.
hash + tag_id more often but hash + tag_id + timestamp should be unique.


Hash is an item you can tag several times with the same tag. It will be stored in the base table and thats why i can't make hash+tag_id to an uniqe key.
The tags table just stores the Tag´s name and the User Ownership.

So if i want count how many Hash Items and how they are tagged i would do:

SELECT count(*),tag_id FROM base group by tag_id

and fast like it should be

if i want to see just tags from a user i have to join the tags table:

SELECT count(*),tag_id FROM base JOIN tags USING(tag_id) WHERE usr_id=1 group by tag_id

still fast

Now, I want to see how many items tagged with tag_id 75

SELECT count(*),tags.tag_id FROM base JOIN tags USING(tag_id) WHERE usr_id=1 AND tag_id=75 group by tags.tag_id

now comes the tricky part. I want to see what other tags these hash items got, based on tag_id 75

SELECT count(*),tags.tag_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 = 1 
AND REV0.tag_id=75 
GROUP BY tags.tag_id

and now hell breaks loose and performance drops rapidly



Edited 4 time(s). Last edit at 11/02/2011 12:48PM by leo mueller.