Menu
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!
932
MyISAM will out-perform InnoDB on large tables that require vastly more read activity versus write activity. MyISAM's readabilities outshine InnoDB because locking the entire table is quicker than figuring out which rows are locked in the table.
InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases. (The CREATE TABLE statement in MySQL 8.0 creates InnoDB tables by default.)
Today, InnoDB is faster in most situations. Assuming you have at least 4GB of RAM... If all-MyISAM, key_buffer_size should be about 20% of RAM; innodb_buffer_pool_size should be 0. If all-InnoDB, key_buffer_size should be, say, only 20MB; innodb_buffer_pool_size should be about 70% of RAM.
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.
107
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
