MySQL query optimization

[specterious]

This is for a document mangement script I've been working on.

I have 4 tables so far:
users
documents
photos
logins

For each user i'd like to count up the # of documents, photos, and logins. They are related by a userid (primary in the users table).

The first thing I did was query the users table selecting * and looping through it querying each of the other 3 tables counting the rows with that userid.
This took about 0.06 seconds.

Then I made this query:

select users.*,
count( distinct documents.documentid ) as documents,
count( distinct photos.photoid ) as photos,
count( distinct logins.loginid ) as logins
from users
left join documents on documents.userid = users.userid
left join photos on photos.userid = users.userid
left join logins on logins.userid = users.userid
group by users.userid

This took 0.18 seconds. I thought it would be faster using 1 query instead of 3 times the # of rows (about 20).

 

[Cardboard Hammer]

That collection of joins results in an explosion of rows to be processed in grouping. Per user, you get their documents * photos * logins rows (since you're using left joins, if they have 0 in any category, the multiplier is 1 for that category.) That'll get really nasty in a hurry.

 

[Cardboard Hammer]

I don't know if MySQL supports this, but it's worth a try. (I primarily use SQL Server, which supports both your query and this one, which is much more efficient.)

select users.*, d.cnt as documents,
p.cnt as photos,
l.cnt as logins
from users
left join (SELECT userid, cnt(*) FROM documents GROUP BY userid) d on d.userid = users.userid
left join (SELECT userid, cnt(*) FROM photos GROUP BY userid) p on p.userid = users.userid
left join (SELECT userid, cnt(*) FROM logins GROUP BY userid) l on l.userid = users.userid

 

[specterious]

Oh wow that is much faster. Down to 0.003 seconds.

Thanks

 

[specterious]

One more query I'd like to merge into this one is getting the last login time for each user.

I tried this:

select users.*,
d.cnt as documents,
p.cnt as photos,
l.cnt as logins,
ll.timestamp as lastlogin
from users
left join (SELECT userid, count(*) as cnt FROM documents GROUP BY userid) d on d.userid = users.userid
left join (SELECT userid, count(*) as cnt FROM photos GROUP BY userid) p on p.userid = users.userid
left join (SELECT userid, count(*) as cnt FROM logins GROUP BY userid) l on l.userid = users.userid
left join (SELECT userid, timestamp FROM logins GROUP BY userid ORDER BY timestamp DESC) ll on ll.userid = users.userid

That seems to get me the first login for each user and if I use LIMIT 1 of course I only get 1 result for the entire query.

 

[specterious]

I think I figured it out. Using MAX() seems to get the highest timestamp value.

select users.*,
d.cnt as documents,
p.cnt as photos,
l.cnt as logins,
l.datetime as lastlogin
from users
left join (SELECT userid, count(*) as cnt FROM documents GROUP BY userid) d on d.userid = users.userid
left join (SELECT userid, count(*) as cnt FROM photos GROUP BY userid) p on p.userid = users.userid
left join (SELECT userid, count(*) as cnt, MAX(datetime) as datetime FROM logins GROUP BY userid) l on l.userid = users.userid

 

[pr0pensity]

Add an extra column in the users table that tallies the photos, documents, and logins each time one is created.

 

[Cardboard Hammer]

Oh wow that is much faster. Down to 0.003 seconds.

Thanks

You're welcome.

 

[Cardboard Hammer]

Add an extra column in the users table that tallies the photos, documents, and logins each time one is created.

It'd take 3 extra columns (4 if you include the addition of last login time to the query) and with the overhead of keeping those columns updated, the total cost would likely outweigh the cost of the more complex query (unless it's being run with relatively high frequency.)

Good indexes on the tables, if not already there, can boost the performance / allow the system to scale better.