Archive for January 12th, 2010
From Stack Overflow:
Say you want to display the latest visitors on a users profile page.
How would you structure this?
Perhaps a table called
uservisitors userid visitorid time
And how would you select this with MySQL without any duplicates?
What I mean is if user 1 visits user 2's profile, then 5 minutes later visits it again, I don't want to show both entries: only the latest.
There are two approaches to this.
First one would be just aggregating the visits, finding the max time and ordering on it. Something like this:
SELECT visitorid, MAX(time) AS lastvisit FROM uservisitors WHERE userid = 1 GROUP BY userid, visitorid ORDER BY lastvisit DESC LIMIT 5
However, there is a little problem with this solution.
Despite the fact that MySQL (with proper indexing) uses
INDEX FOR GROUP-BY optimization for this query, it will still have to sort on
MAX(time) to find 5 latest records.
This will require sorting the whole resultset which will be huge if the service is heavily loaded.
Let's test it on a sample table:
Read the rest of this entry »