query help...join and count

[vicG]

Hi all,

User table has an ipAddress column, & userid column as pk...maybe more than one user has the same ip!

UserActivity table has userid as fk, and activityid which is fk to activity table

So, I'm trying to get a count of how many rows in the user activity table (for a given activity id) have a userid which matches _any_ user with a given ip.

something like this? ( but correct and using joins, since my subselects are always crazy slow )

select count(*) from useractivity where userid in ( select userid from useractivity where activityid = x and user table.ip = Y )

Thanks for any help!

Vic

[rch1231]

Ok based on what you posted the query should look something like this:

SELECT
UserTable.IPAddress,
UserActivity.UserID,
Activity.Activity,
Count(UserTable.UserId) AS UserCount
FROM
UserTable
Inner Join UserActivity ON UserTable.UserId = UserActivity.UserID
Inner Join Activity ON UserActivity.ActivityID = Activity.ActivityID
WHERE
UserTable.IPAddress =  'X' AND
UserActivity.ActivityID =  'Y'
GROUP BY
UserTable.IPAddress,
UserActivity.UserID,
Activity.Activity

[vicG]

Hi rch

Thanks for the help.

I just ran the query, and the results seem strange...

Can I ask you a question -- why does the query count the usertable.userid? Shouldn't it count the rows in the user activity table?

Just trying to understand the magic :)

v

[rch1231]

I may have selected the wrong field there and I think you are right. My excuse is going to be "I typed it in freehand with no tables and I picked the wrong field." LOL.

I think you get the idea from it though. If you want to verify the data then remove the count() and look at the individual rows. If it is a lot of data add LIMIT 100 to both queries and check it.

[tesuji]

Hello

Well, you could get better help if you were more precise on which tables exist and, important too, how they are related to. As rch1231 already did I also see some of these objects:

1st usertable pk(ipaddress, userid) ?
2nd useractivitytable fk(ipaddresse, userid), what pk ?
3rd activitytable pk(activityid) ?

If so, there might be a many-to-many relationship between usertable and activitytable which could be given by useractivitytable. To form the many-to-many relationship correctly the linking table useractivitytable must have: fk((ipaddress, userid) AND fk(activityid).

At least both foreign keys must be part of useractivitytable´s primary key: pk(ipaddress, userid, activityid). If those keys were correctly definded, you wouldn´t get slow sub-selects or joins (assuming there are some 10000 or better some 100000 entries in the linking table at least. Hint: Fetching 20% of about 1000.000 entries in useractivitytable should last about 1 to 2 seconds on mysql database (innodb) + medium-power computer).

However, I am wondering why ipaddress is part of the user´s primary key. If you are interested, we can discuss this until your tables and relationships are clearly known.

-- tesu

[vicG]

Thanks, rch, I'll try removing the count and maybe changing it, hopefully I'll see the light!

tesu, sorry about the confusion. PK's for all tables are auto-increment id fields named for the table -- userid, useractivityid, activityid. IP address is not part of any pk.

Useractivityid is related to user and activity tables because each row has userid and activityid as fk to those tables.

As for speed, rch's join was fast, but my sub-selects are much slower. I would have thought it would all "compile" (I'm assuming something like that happens with queries) to the same thing, but obviously not...

Thanks!