MySQL innodb or MyISAM

[pascal_22]

Hello to all!

 

A friend of mine ask what is the difference between mysql table innodb and MyISAM.....

 

A have made a fiew search but i can't get the real answer ...

 

Also, i check the database of my website and 50% are innodb and 50 % are MyISAM....

 

I made a lot a select and insert...  have a few update....

 

Do you have an advice to give me?

 

Thanks!

 

Pascal

[Ch0cu3r]

First google search for difference between mysql table innodb and MyISAM

http://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam

 

looks very informative and answers your question nicely.

[vinny42]

A lot has been said about this, as you can see in the posted link.

 

In the end though, there's only one thing that counts: reliability. MyISAM doesn't do crash recovery, it doesn't even do consistency checks runtime. If doesn't do foreign-keys and it doesn't do transactions. In short: MyISAM has no mechanism for making your data reliable.

 

Worse, if you mix MyISAM and InnoDB, your InnoDB tables will do transactions, and your MyISAM will pretend to do transactions,and all your data goes down the drain.

 

If you must use MySQL then *allways* use InnoDB. Some people will insist that MyISAM is faster but what good is a fast database if you cannot trust it to store your data reliably?

[gizmola]

The myiasm engine is the default storage engine. It's lightweight and lacks many features that other Relational databases have. In particular it lacks referential integrity constraints, support for ACID/transactions (commit or rollback) and it does not offer row level locking.

 

It does not provide a "data cache" (it's query cache simply caches statements) nor a transaction log. Most databases offer a transaction log feature so that if the database server goes down, you can recover quickly and cleanly. MyIsam instead provides tools to check for corruption of tables and indexes and repair them. This works ok, so long as your database doesn't get really large, but you can certainly find horror stories out there from people with really large myisam databases, and how it took days or weeks to recover from a server outage.

 

Innodb offers all of those features, and also implements "clustered indexes" which is a fancy term for having all the data in the tables stored in primary key order, so that any data read using the primary key, has also read the entire row's data, as opposed to having a separate index file which has to be referenced and then a seperate read/seek of the datafile.

 

In short, I would recommend using innodb for all your tables, however, many of the features I've discussed require an understanding of them, and in some cases configuration or code changes to take advantage of them. For example, if you have no constraints defined, having foreign key constraints doesn't do you any good. You would have to add them. You won't utilize transactions if you haven't coded your application to start a transaction and commit on success or rollback on failure. You do get row level locking for free just by using innodb, which can be important if you have tables with mixed use (lots of selects, with frequent updates or deletes).

 

If your server has adequate resources and you can allocate memory to the innodb data cache known as the "buffer pool" (See http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html) you can have most of your queries serviced out of the memory cache, which of course is a huge performance improvement over reading from disk.

 

I hope this helps you in the process of learning more about innodb.

[vinny42]

 

The myiasm engine is the default storage engine.

 

It was, until 5.5 when MySQl finallt woke up to the real world. :-)

 

 

For example, if you have no constraints defined, having foreign key constraints doesn't do you any good. 

 

If you have no contraints defined you have a very strange database design :-)

 

But, more to the point, features that are not used do not slow the rest down. This appears to be a common misconception; the fact that a database can do something doesn't mean it's slower than a database that can't do something.

[jcbones]

IIRC, Innodb doesn't do full text searches, but MyISAM does.  That would be the only reason, I can think of, for someone to use MyISAM, if they needed that functionality.

[vinny42]

 

Innodb doesn't do full text searches, but MyISAM does.  

 

And even there it's common practice to use InnoDB for the storage and use a trigger to sync it with a MyISAM table for the fulltext search columns.

Another reason why I wonder why so many people keep using MySQL :-)

[gizmola]

If you have no contraints defined you have a very strange database design :-)

 

But, more to the point, features that are not used do not slow the rest down. This appears to be a common misconception; the fact that a database can do something doesn't mean it's slower than a database that can't do something.

That was not my point. Even if you started with constraint definitions in your CREATE TABLE DDL, MyISAM will accept those statements and blissfully ignore them. So simply altering a table to use Innodb doesn't magically cause RI to start working. You have to go back and run those statements again after all the tables are normalized, or craft ALTER TABLE statements. This is important for someone who is still learning about mysql to understand.

[vinny42]

 

That was not my point. Even if you started with constraint definitions in your CREATE TABLE DDL, MyISAM will accept those statements and blissfully ignore them. So simply altering a table to use Innodb doesn't magically cause RI to start working. You have to go back and run those statements again after all the tables are normalized, or craft ALTER TABLE statements. This is important for someone who is still learning about mysql to understand. 

 

I did not get that from your post :-)

 

But it is absolutely true, MyISAM will silently fail lots of things that other engines do support. One of the many reasons why the whole multi-engine thing is a very bad idea.

[pascal_22]

Thanks to all!!! it's a very helping post!!

I appreciate your help and your time you take to answer my question!

 

Thanks!!!