Should we be using InnoDB instead of MyIsam?

[rickb]

There has been remarkably little discussion of InnoDB on this forum.  I have used a range of systems with MySQL as the database and I like CMS Made Simple for its simplicity.  But isn't it the case that InnoDB serves better the need of a multi-user CMS than the older MyISAM engine?

Pros and cons include:

* MyISAM can be faster because it is intrinsically simpler.

* InnoDB can be faster because indexing is done better.

* InnoDB properly supports foreign keys so constraints can be enforced by the DB and thus ensure that relationships between tables remain consistent. This isn't provided by MyISAM.

* InnoDB supports cascading deletes - this is easier for the developer and gives a guarantee that related tables remain consistent.

* InnoDB supports transactions that allow consistent behaviour when more than one person is making changes. This isn't provided by MyISAM.

For two other examples, the Symfony Project (http://www.symfony-project.org/) uses InnoDB exclusively, whilst Drupal (http://drupal.org/) has the same approach as CMSMS - it's up to you to decide for yourself and the default choice is the older, simpler but less robust technology.  This choice is perhaps a bit like choosing between PHP4 and PHP5 - now most people would agree that PHP5 is the way forward.  If people agree, maybe it would be better for CMSMS to create InnoDB tables by default - or perhaps ask the person who is installing a new system what they need.

Rick

Further reading: http://en.wikipedia.org/wiki/Innodb, http://dev.mysql.com/doc/refman/5.1/en/ ... rview.html http://www.mysqltutorial.org/understand ... yisam.aspx

PS I've attached the script I used to convert my DB.

[Ted]

2.0 and the Silk Framework will use InnoDB by default, because of all those reasons you've mentioned.  I'd love to switch to it now and use transactions and stuff, but since we still support older MySQL versions, it doesn't really work for now.

[rickb]

I'm glad to hear we can look forward to this improvement.

Is it backward compatible?  I mean, if we design the schema with the necessary foreign key relationships etc, then those with a newish MySQL will get substantial benefits.  Then, for those who have older MySQL, the installer can detect that and revert to the MyISAM engine as used now.  Is that realistic?

Rick

PS how important is it to support MySQL 3 or 4?  I haven't used them for some years now and I don't really have a feel for how widespread they are.  It's a similar question to that of whether to support PHP4.  The http://GoPHP5.org/ campaign was all about breaking the chicken/egg problem that support for the old technology is demanded by backward compatibility but support for the old technology severely limits the adoption of the new.  CMSMS hasn't embraced the GoPHP5 thinking.

[Ted]

Well, it's backwards compatible, but the reason I care more than anything else is transactions -- I honestly don't use foreign keys much (for the same reason I really don't like non-dynamic languages).  So until we can use transactions throughout the system, it's not a huge deal (well, to me anyway).

Yes, we did support the gophp5 initiative, but it was for 2.0 and future releases.  http://blog.cmsmadesimple.org/2007/07/08/gophp5/

[rickb]

Well, it's backwards compatible, but the reason I care more than anything else is transactions -- I honestly don't use foreign keys much (for the same reason I really don't like non-dynamic languages).  So until we can use transactions throughout the system, it's not a huge deal (well, to me anyway).

I don't understand your reasons - perhaps because I've spent too long writing Ada/C++/Occam/Java/Pascal/other compiled languages.  I guess we'll have to disagree on that one.  You always have implicit foreign keys just by forming relationships between tables.  There's no compulsion to use explicit foreign keys but they do provide useful consistency checks and help to document how the schema is intended to work.  And delete-cascade saves effort.

Yes, we did support the gophp5 initiative, but it was for 2.0 and future releases.  http://blog.cmsmadesimple.org/2007/07/08/gophp5/

That's good news.      Any idea when 2.0 is expected?
Is there a GoMySQL5 initiative we could join?  

Rick

[fatlizard]

2.0 and the Silk Framework will use InnoDB by default, because of all those reasons you've mentioned.  I'd love to switch to it now and use transactions and stuff, but since we still support older MySQL versions, it doesn't really work for now.

Does this also mean that in future CMSMS will be dropping the methodology of using sequence tables, and using auto increment for table indexes?

Fat Lizard

[calguy1000]

we haven't done anything NEW with sequence tables for a long time... the sequence tables are just a legacy from when databases like postgres didn't support auto increment, etc.

[rickb]

All the main DB flavours now support auto-increment in some way or other ...

... except Oracle - what a bizarre omission!

R.