large databases

Question

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.

I question whether a relational database is a good fit for this problem, not that I'm an expert on that issue. — jpmc26, Dec 28 '12 at 21:50
@Juhana: Ok, I'll bite! How about a separate database for every user! — Gerrat, Dec 28 '12 at 21:58
Thank you very much for your help. I took now a innoDB and I hope that it works out like this. — atreju, Dec 30 '12 at 03:07

score 3 · Accepted Answer · edited May 23 '17 at 12:27

3

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.

edited May 23 '17 at 12:27

Community

1
1

answered Dec 28 '12 at 21:52

Trent Earl

3,517
1
15
20

Appropriate indices are a must if using a relational database table. – jpmc26 Dec 28 '12 at 21:53

score 1 · Answer 2 · answered Dec 28 '12 at 21:52

It depends

If you start having one table per user (aka sharding) you will have some troubles at the beginning.

if you don't have the need of scale right now. go for 1 table with good indexes. I wouldn't use MyISAM but InnoDB instead otherwise you can get hit by the bigests issue of MyISAM (locks...)

score 1 · Answer 3 · answered Dec 28 '12 at 22:03

The normal relational design for this would, I think, use three tables:

Users — user ID, and other attributes: name, email, etc
Vocabulary — least clear from the question, but presumably words with attributes such as part of speech and maybe meaning, probably including a word ID (because some word spellings have multiple meanings).
User_Vocabulary — a table with a User ID, Word ID, and maybe attributes such as 'date learned'.

If MyISAM locks the table while a query is going on, then you can't afford to use MyISAM if you need concurrent updates to the User_Vocabulary table. So, go with InnoDB for all the tables.

large databases

3 Answers3