I have an online service (online vocabulary trainer). Each user has its vocabulary.
Now, I'm not sure, how I should structure my Mysql-DB.
As far as I know, I have different possibilities:
everything in one table (MyISAM): I store all the vocabulary in one large MyISAM-table and add a column "userid" to identify each user's vocabulary
every user has its own table (MyISAM): Every time, when a user is created, the programm adds a table named like "vocabulary_{userid}" where {userid} is to connect the table to a user.
everything in one table (InnoDB): Like point one, but with InnoDB instead of MyISAM.
The problem is, that one large vocabulary table can reach up to 100 millions rows. With MyISAM, the problem is, that every query locks the whole table. So I imagine, if there are many users online (and send many queries), the table might be locked a lot. And with InnoDB, I'm simply not sure, wheather this is a good solution as I'm having quite some SELECT-, UPDATE-, and INSERT- commands.
I hope anyone can help me. Thank you in advance.
It is almost always better to go with InnoDB. InnoDB can handle 100 milllions rows, the max size is 64tb.
It doesn't sound like you have a relational dataset, but more of a key/value store. Maybe Riak is a better solution.