Which MySQL storage engine will be better in my situation

Question

I have to develop a big application, a School management system; which MySQL storage engine will be better? MyIsam or InnoDB or any other?

Question is not Descriptive enough. Use INNODB as it supports foreign key — Naveen Kumar, Feb 27 '12 at 08:43
I've got news for you. Unless your school has a billion students, it's _not_ going to be considered big in the DB world :-) — paxdiablo, Feb 27 '12 at 08:45
@paxdiablo, 1 or a billion, its just a School Management System. It is really that big — Starx, Feb 27 '12 at 08:57

score 2 · Answer 1 · edited May 23 '17 at 12:32

InnoDb Support Relationships and MyISAM does not. If your application require this features, you should use InnoDB. Other major differences includes:

InnoDB is newer while MyISAM is older.
InnoDB is more complex while MyISAM is simpler.
InnoDB is more strict in data integrity while MyISAM is loose.
InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
InnoDB has transactions while MyISAM does not.
InnoDB has foreign keys and relationship contraints while MyISAM does not.
InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
MyISAM has full-text search index while InnoDB has not.

^{By Yang Yang source}

See this question and find out yourself, which fits better in the situation.

score 2 · Answer 2 · answered Feb 27 '12 at 08:43

2

Go through the following link, where you can have a better understanding to use innodb or MyISAM.

http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/

http://www.mikebernat.com/blog/MySQL_-_InnoDB_vs_MyISAM

answered Feb 27 '12 at 08:43

jyotiprakash

2,086
1
20
26

score 1 · Answer 3 · answered Feb 27 '12 at 09:00

First of all let me tell you that for such kind of management systems concurrency is not a major issue because you're not going to have simultaneous transactions going on. Hence the storage mechanism that u have mentioned, InnoDB, has more than enough features like ACID compliance,crash recovery, row level locking etc. for ur application, but that doesn't mean InnoDB is better.

The main issue is Data-warehousing coz in such systems data grows to a great extent. Hence to fulfill this need Mylsam looks appropriate as it is the default storage engine as well as used widely in data-warehousing environments.

Merge or MRG_MylSam is another alternative. It enables you to logically group series of identical MylSam tables and reference them as one object. Also very good for VLDB(Very Large Database) environments.

Some other storage engines are NDBCluster, Federated, Archive etc. which i don't think u need... Good Luck..!!

The year is 2012. No one should be mentioning Merge or Federated. I also disagree strongly that the OP should be thinking about data warehousing, and MyISAM is neither the default storage engine (it's InnoDB for a long time now) nor a good choice for anything in question (ever repaired a really big MyISAM table after a crash? Good luck). — , Feb 27 '12 at 15:24

score 0 · Answer 4 · answered Feb 27 '12 at 08:46

0

You didn't give much information about your task. Most likely you'll have related tables (innodb supports foreign keys), besides innodb use more effective blocking in concurrent enviroment that myisam. So using InnoDb as default engine is reasonable.

answered Feb 27 '12 at 08:46

Oleg

2,733
7
39
62

score 0 · Answer 5 · answered Feb 27 '12 at 09:21

Use InnoDB, its performance has come along way and it also supports foreign keys.

Also target the latest MySQL verison 5.5.x at the time of writing. When the performance of Vanilla MySQL from Oracle becomes a problem you can then look at alternative servers like Percona (http://percona.com) which are drop in replacements.

BTW from MySQL 5.5 InnoDB is the default storage engine in MySQL servers

Which MySQL storage engine will be better in my situation

5 Answers5