Conclusion: the usermedia table should be distributed to many tables. How many? This should be configurable. I think 200 will be okay (50k users x 200 media each = 10 000 000 rows in usermedia = 50 000 rows in random usermediaX on average).
Basically, when we need to refer to usermedia asking questions like "Hey, what's the anime list of rr-?" the query that looks like SELECT * FROM usermedia WHERE ... should be changed for instance to SELECT * FROM usermedia4 WHERE ...
Proposed mechanisms on how to know which table to refer to:
randomly choose usermediaX table whenever user is processed and remember it
pros: none?
cons: I have to remember in $user corresponding table name, it also means adding new column to DB and removal of user media becomes bogus and probably insecure
choose usermediaX table using hashed user name
pros: easy to use, retrieving table name can be made into helper, processing user lists doesn't have to do anything hacky
cons: difficult to implement with regard to parametrization of usermediaX table count
The last approach can be changed in such way that instead of distributing user media over multiple tables, we can distribute users over multiple database files. Then we can attach only database we need. This is probably best approach.
Just remember when implementing this that stuff like cron-globals.php needs to deal with usermedia in global manner without user-specific queries. Thus all global usermedia processing needs to be changed as well.
Note that queries like 'search for users that...' will be difficult. So far we use such query only in globals IIRC.
http://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file
Conclusion: the
usermediatable should be distributed to many tables. How many? This should be configurable. I think 200 will be okay (50k users x 200 media each = 10 000 000 rows inusermedia= 50 000 rows in randomusermediaXon average).Basically, when we need to refer to
usermediaasking questions like "Hey, what's the anime list of rr-?" the query that looks likeSELECT * FROM usermedia WHERE ...should be changed for instance toSELECT * FROM usermedia4 WHERE ...Proposed mechanisms on how to know which table to refer to:
usermediaXtable whenever user is processed and remember it pros: none? cons: I have to remember in$usercorresponding table name, it also means adding new column to DB and removal of user media becomes bogus and probably insecureusermediaXtable using hashed user name pros: easy to use, retrieving table name can be made into helper, processing user lists doesn't have to do anything hacky cons: difficult to implement with regard to parametrization ofusermediaXtable countThe last approach can be changed in such way that instead of distributing user media over multiple tables, we can distribute users over multiple database files. Then we can attach only database we need. This is probably best approach.
Just remember when implementing this that stuff like
cron-globals.phpneeds to deal withusermediain global manner without user-specific queries. Thus all globalusermediaprocessing needs to be changed as well.Note that queries like 'search for users that...' will be difficult. So far we use such query only in globals IIRC.