What storage engine do I need - Mysql? I have designed a database and came to the point of actually deciding which storage engine is better for my case.

I estimate that during the first few years I would have around 100,000 - 500,000 users. Some of the users can also have multiple posts, comments, etc. The number of users can go up to maybe a max of 10,000,000. Well I am just estimating.

I am planning to use a BIGINT for my user_id or in a certain situation I may have to go for a different route where I would store a user_id as a string like '000123456789'. I am not really decided about that.

My CRUD operation estimates are like this:
Create 15%
Read 60%
Update 20%
Delete 5%  

So my focus is on Read and the Update.
If you want a proper database then of the two you should go for innodb - it supports ACID transactions, referential integrity, and smaller granularity locking.

myISAM is faster in a number of read-only use cases but:


The lack of support for transaction safety can lead to corruption in multi-user situations where several updates happen at the same time unless you are very careful in your application design
The lack of foreign key support removes a valuable data integrity protection from your armoury
The fact the writes result in a full table lock can cause massive performance issues for use cases that involve much by way of insert and update operations.


innodb is currently the default selected if you do not specify a storage engine, unless you are running an old or badly configured instance of mysql (myisam used to be the default).

Update: as mentioned by akuzminsky in the comments, it may no longer be the case that myISAM is a better performer for any real world workload. A quick search for "innodb myisam benchmarks" seems to suggest this is the case.