I'm creating a web directory that will allow individual users to signup for an account and store essentially text documents in a mysql database entry.

Initially there will likely be only a few hundred users, but we hope to have 10,000 to 100,000 at some point. And each user would be able to upload 100-200 "documents".

Would it be more efficient to create one massive table, indexed by a user number? Which could theoretically grow to be 20,000,000 entries. Or to go ahead and create a table for each user with their individual documents?

I'm assuming it's not healthy to have thousands of tables in a database, but I really can't find any concrete data on this.

up vote 7 down vote accepted

MySQL can easily cope with 20M rows if it's properly indexed. We have tables with more than a billion rows in it.

Having one table is cleaner. Don't need to do magic in application based the user(name). Easier to make any statistics on the documents table too.

I would definitely go with the one big table approach. If you are concerned about the table (physical) size you should consider partitioning the documents table. http://dev.mysql.com/doc/refman/5.5/en/partitioning-types.html

  • Thanks for the reply. I'll definitely go with a single table then, and I'll research the partitioning approach. One question though, what exactly is meant by a properly indexed table? I hear this referenced a lot, and assume it means that the database table needs a properly defined index key. But is there more to it than that for the best optimization. – Keith Aug 2 '13 at 10:20
  • With properly indexed I meant having at least a composite index on the user_id with the columns you are filtering for or ordering by on the document table and index on the username on the user table (probably a partial index is enough check for cardinality 90-95% is enough). For example: sqlfiddle.com/#!2/9fb15/2 (In my case with partial index 5 on username cardinality is 50%) – Károly Nagy Aug 2 '13 at 10:40
  • I think I understand, thanks for the help. One more question, assuming you have a primary index key for each table, does it still help optimization to define additional columns you know you'll be searching for regularly (such as parent category) as an index? Is there a downside to defining a primary or unique key and 2-4 indexes for each table? – Keith Aug 2 '13 at 11:08
  • Indexes will help, yes. Actually you should always have indexes on the columns you're filtering on otherwise the query will end up in full scan search. The only downside (besides of the index size) is slower inserts and updates however since 5.1 with InnoDB plugin and 5.5 by default MySQL has fast index creation (dev.mysql.com/doc/refman/5.5/en/…) so it's not a big issue anymore. – Károly Nagy Aug 2 '13 at 11:17

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.