24

I read the following article yesterday (http://blogs.sitepoint.com/2010/11/19/mysql-mistakes-php-developers/) and it wrote the following:

MySQL has a number of database engines but you’re most likely to encounter MyISAM and InnoDB.

MyISAM is used by default. However, unless you’re creating a very simple or experimental database, it’s almost certainly the wrong choice! MyISAM doesn’t support foreign key constraints or transactions which are essential for data integrity. In addition, the whole table is locked whenever a record is inserted or updated: it causes a detrimental effect on performance as usage grows.

The solution is simple: use InnoDB.

I've always used MyISAM because it was the default. What do you think?

If I were to upgrade to InnoDB, in phpMyAdmin, can I just edit each table and change it to innoDB or is there a more complex process to perform?

Thanks!

Community
  • 1
  • 1
Ben Sinclair
  • 3,896
  • 7
  • 54
  • 94
  • 1
    What do I think? That is this a duplicate of [this question](http://stackoverflow.com/questions/20148/myisam-versus-innodb) + [this one](http://stackoverflow.com/questions/225150/are-there-any-pitfalls-things-you-need-to-know-when-changing-from-myisam-to-inn). :) – Dan J Nov 22 '10 at 23:16
  • 1
    myisam isnt the default engine anymore http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html. what do i think as to your rationale for choosing an architecture/engine solely based on that fact that it's the default one - ummmm no comment. – Jon Black Nov 22 '10 at 23:34

2 Answers2

30

Yes, you can swap in and out engines like used underwear, if you like, without much trouble. Just change it in phpmyadmin.

But I wouldn't change it for the sake of changing it. Do you need to use foreign keys? Change it. Do you need row-level-locking instead of table-locking? Change it.

It's worth noting that there are good reasons to use MyISAM, too. Take a look at FULLTEXT indexing. You can't do that with InnoDB.

UPDATE

As of MySQL 5.6 FULLTEXT has been implemented for InnoDB tables as well. Here is the manual.

Savas Vedova
  • 5,622
  • 2
  • 28
  • 44
Stephen
  • 18,827
  • 9
  • 60
  • 98
  • +1 - At this point i wouldnt change it just for the sake of changing it. Only change it if you need InnoDB's features. That said, I myself havent used MyISAM in a very long time. – prodigitalson Nov 22 '10 at 23:22
  • 1
    I use InnoDB almost exclusively. Except the other day when I needed a FULLTEXT index. ;) – Stephen Nov 22 '10 at 23:30
  • 4
    Hmmm so many opinions on the matter. I think I'm just going to start with MyISAM as i've never had a problem for the last 7 years using it :) – Ben Sinclair Nov 24 '10 at 05:00
  • 4
    It should be noted that InnoDB from MySQL 5.6 onwards DOES support Full Text Search – Chris Aug 15 '13 at 08:03
1

Sorry for bumping an old question, but this also helped me a lot to choose which engine, especially since MyISAM is faster for reads and my database tables had more read the write:

http://www.rackspace.com/knowledge_center/article/mysql-engines-myisam-vs-innodb

NaturalBornCamper
  • 3,675
  • 5
  • 39
  • 58