Hi

I have been researching the differences (implementation as well as performance) between MyISAM and InnoDB for sometime. It looks like we need to hit a subtle balance between both of them,

In particular, it was very disappointing for me to read that Dani had to revert back from InnoDB.

The right way to use the mix, IMO, is to use a master-slave configuration for database replication. One should use InnoDB on the master to store tables for fast concurrent inserts and updates. The same table should be replicated on the slave using MyISAM for fast reads.

Also, when using InnoDB, don't forget to play around with the innodb_buffer_pool and innodb_thread_concurrency parameters. They seriously impact the performance you get out of InnoDB setup.

One last point to note here is that InnoBase has been acquired by Oracle already. So, future bug fixes and improvements may not be free.

Hope this helps!!

I hope they are not going to do so. Read the Oracle statement.

BTW, SUN has acquired MySQL...

this post is very helpful.thanks

I've been asking myself this same question but aren't you forgetting some important differences between Inno and MyIsam here?

As far as I know MyIsam does NOT support Foreign keys and InnoDB does. Which makes a huge difference in your coding / db design.

That is the main reason I'm struggling with the question, to switch or not to switch, as when I'm going to InnoDB I should redesign my database (just partially but still some extra commands are necessary to define the FK's) and more work is changing the (php / sql) code (where you don't have to do FK relations manually anymore)

this thread was very helpful thanks a lot dani

There is another very good reason to use INNODB: concurrency. If you are going o have multiple users updating (different rows of) the same table concurrently, you really need the row locking functionality of INNODB.

I had really only ever seen the MyISAM engine in use but when I installed MySQL Workbench it defaults to the innodb engine and that led me to do the same research you did and I concluded exactly what you did:

MyISAM is faster but innodb is better for databases who have lots and lots of writes and I think the vast majority are as you describe daniweb to be... mostly reads.

Yep, the conclusion is:
if your table is mainly Read: MyISAM better because faster
if your table is mainly Write: InnoDB better because faster, especially for concurrency:
MyISAM or InnoDB?

You can't do full text search with InnoDB, but you can search using more resourse heavy methods such as LIKE '% %'

This is really nice read, but it has included only few points.