One table for all users or separate table for each user in MySQL 10M records db

Question

I am working on the website that could deal with 1000s of images for my clients. Say 20,000 files per user, circa 200 users (with hopes for 1000s soon).

• Images are represented by vFile (unique id) records in MySQL InnoDB database describing ownership and permissions, location in the folder tree, location of physical files on S3, size etc.
• Users can create, delete, reorder etc those vFiles.
• Each user can alter only vFiles he owns.

The database dilemma is:

• all the vFile records in one table or
• separate table for each user.

Second solution seems to have a few advantages:

• as queries will search for vFiles for one user only, second solution reduces load on the db.
• operations on vFiles (like renaming, deleting, reordering) will affect much smaller tables.

Your thoughts would be appreciated.

Answer 1

The second solution does not seem to account for future changes to site’s features. For instance, what if at a later date you would like to allow users to share images, or allow for image pools with multiple users having different permission levels to a file? If you went with solution #2, adding those features would result in a lot of duplicate entries. In general, if you have identical information most things I have read suggest putting it in one table and then adding a linking table for flexibility. Selecting the items that matched user_id x in the linking table should be very fast, even with a third column for additional filtering permissions.

Also, even if you have a huge single table search, adding indexes to the relevant columns would speed up the search process greatly. The only situation I could think of where #2 might be better is if you locked tables.

I’m not an expert though, so I would like to hear what everyone else thinks.

Answer 2

Use the following table structure

Put

    User Id ownership, permissions, location size etc imageFileIndex

in one table and in another table

    imageFileIndex vFile