Mysql – Quite large (400k) thesql database design for multiple users

database-designMySQL

I'm seeking advice of experienced admins.

I'm working on a website, where you solve word anagrams. If it solved it should never be displayed again.

Wordbase contains ~400k entries. What would be the most effective solution to storing such data?

One way could be:

+---------+------------------------+
| word_id | user1 | user2 | user...|
+---------+------------------------+
|  1      | null  |  null |  1     |
|  2      | 1     |  null |  null  |
|  ...    |       |       |        |
|  400000 | null  |  1    |  null  |
+---------+------------------------+

Where let's say 1 = solved.

But wouldn't it become a monster quite quickly?
(+even a simple query of extending it by a new user takes forever)

Other solution is to store every solved word_id for all users, but then, it can be 6-digits for every entry and growing massively and rapidly aswell.

Also which engine would be more effective in this example? MyISAM or InnoDB?

Best Answer

Use (word_id, user_id) table to store solved combinations, that way you won't store the NULLs. How many words will an average user solve? 5-20 maybe? 400k * 20 = 8M rows, thats nothing (the two column table is quite compact).

Do not even try to alter the table on each new user, that would stop working really quickly.

I suggest InnoDB for anything new unless there is a specific reason against it.

Related Question