First I should say the problem is often exaggerated – indeed some users can be 100 times more than others, so what ? With big numbers of say 300000 users per box it all evens out pretty well anyway.

There is however a different problem which is about user “groups” not users individual users. Consider for example you decided to put out box one by one and keep all registrations going to them until it gets 300.000 registrations.

This may be suboptimal because you may find out users are more active first days after registration, it may be even more complicated like users which came through some marketing action may have sustained interest while from other may mostly leave after short period of time.

It is also quite natural for some users to stop using service over time so proportion of active users goes down.

So you need to think and see what kind of allocation policy makes sense in your case. There are many ways to organize it.

Databases are at large extent created to ease the development process and the more database can do to help it the better. Sure frameworks can have their cache but this cache is not good enough in all cases plus you may have different application accessing same data.

Database may not need to guess how you’re using its data but at least follow your recommendation. If you think about it indexes are nothing more but hints to the database – I’m going to use this column for lookups and I want these to be fast. Clustering, partitioning or physical sorting is another ways to speedup certain access paths.

Such as using Squid?

Trying to determine how to partition my data and all that myself seems a bit daunting. I’d be happy with an LRU-style thing. I mean, I will deploy memcached and use that for caching prior to the database anyway. But any further optimizations to MySQL are welcome.

Also note it is not as easy as last access time is the only important factor for data organization – on the contrary there is bunch of other things such as access paths which should be considered.