0

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.

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
6
  • would my BIGINT user_id cause any problems with innodb? (in terms of read performance)
    – Cristian
    Commented Aug 26, 2015 at 16:13
  • Why would it cause read performance issues in innodb but not myisam? It is just an 8-bit value instead of a 4-bit one (which INT would be). Even if there were a noticeable difference in that respect (the only way to tell for sure is to simulate realistic application activity in a database of each type) is it worth losing proper database features (ACID transaction support, referential integrity) over, potentially meaning more coding hassle to account for things these features do automatically for you? And what about the write performance hit of table level locking in myisam?
    – David Spillett
    Commented Aug 26, 2015 at 16:23
  • 2
    It would be nice to see benchmark results that prove "myISAM is faster in a number of read-only use cases" point.
    – akuzminsky
    Commented Aug 26, 2015 at 19:42
  • "MyISAM is faster" myth is immortal :-)
    – akuzminsky
    Commented Aug 26, 2015 at 19:43
  • @akuzminsky: myISAM certainly used to faster for some workloads that were just many simple reads, presumably by virtue of simply being simpler because it isn't supporting granular locks and so forth. It was always slower for any workload that involved inserts/updates especially with any sort of concurrency, and for more complex selects. Of course things may have changed since I last used mySQL for any real work (which was quite some years ago), and a quick scan search for "innodb myisam benchmarks" seems to suggest that is the case.
    – David Spillett
    Commented Aug 27, 2015 at 8:47
1

I am not sure when but sooner or later we're going to see deprecation of MyISAM.

InnoDB is performing well and it does have fulltext as well (though performance is a bit of something being worked over there.) InnoDB is even a default storage engine in latest MySQL versions.

If you really really need performance from FULLTEXT go with MyISAM (Note that, you have FULLTEXT in InnoDB and you can try it.)

InnoDB is (default) good choice.

There is one more reason to avoid InnoDB in favour of MyISAM is possibly due to "Storage requirements" (it takes 3x space) but that shouldn't be a reason actually.

There are more reasons to avoid MyISAM than to use it.

Go with InnoDB. (As every one here seems to comment)

Improve this answer
1
  • I guess you are right. Everywhere I see these two engines compared, there aren't any big differences. But when it comes to all the extras InnoDB has over Myisam, it really makes sense to go for InnoDB.
    – Cristian
    Commented Aug 27, 2015 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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