WordPress is designed to run only on MySQL database. So, optimizing MySQL is very important thing if you want to run WordPress as fast as possible on the hardware you use. But, that might not be as straightforward as you may thing, considering that MySQL can used many storage engines.
Since MySQL 5.0, there are 10 (yes, ten) storage engines in MySQL. Before MySQL 5.5 was released, MyISAM was default storage engine, and when you create a new table without specifying engine, table would use MyISAM engine. After you upgrade to MySQL 5.5, default engine is now InnoDB. Great thing about MySQL is that you can use different storage engines for each table.
This is old(est) storage engine in MySQL and most commonly used. This is engine that is easy to setup (no foreign key relationships between tables, or design problems). It is very good in read related operations and it supports full text indexing. But, it has many disadvantages: no transaction support, no data integrity check (no strict table relations) and it supports only full table lock making it slower if you need to update or insert data, because for each update or insert, whole table will be locked making it unavailable for other queries. Because of this, MyISAM is on the average good for most of the WordPress tables. MyISAM works well out of the box, and it doesn’t need too much optimization. Even with optimization, you can’t gain much in terms of performance over default setup.
MyISAM is not very reliable in case of hardware failure, process shutdown or some other disruption. This will almost always cause data corruption depending on the last operations run.
This is relatively new engine and it supports transactions, it is very fast in insert or update operations because it supports row locking allowing for multiple operations over same table and it supports foreign keys for table relations. And all that makes InnoDB great when data integrity is important issue. But, designing tables with foreign key constrains is not always simple, it doesn’t support full text indexing and it needs more resources (memory) that MyISAM. Also, you need to spend some time optimizing this engine. Depending on the level of optimization and hardware used, InnoDB can be set to run very fast, some report even 20 times faster than default setup.
InnoDB doesn’t have constant size for the table. Even when you get table sizes, you need to know that the values returned are only estimates. This comes from different way InnoDB handles data, and because of that it needs more space for data than MyISAM. But, InnoDB is very reliable due to transactional nature of data operations, and makes it very good choice for systems where backup operation are essential and often needed. InnoDB tables are reliable and has many safety measures implemented to make sure that data remains safe.
What engine to choose?
Well, this is not easy thing to answer. Since InnoDB is now default engine (MySQL 5.5), when you install new WordPress on server running latest MySQL, you will most likely get all InnoDB tables. If you have powerful server with a lot of memory, you will not notice any slowdowns (if InnoDB is set properly), and for many operations you may notice speed improvements.
If you have all MyISAM engine tables in the database, you might consider switching some of them to InnoDB engine. There is no right answer here, and it all depends on what you need from your database. By default, WordPress doesn’t use full text indexing, so it makes it OK to use InnoDB. If you need more data reliability, InnoDB is the way to go. Since WordPress doesn’t use foreign keys for table relations, engine choice is also not important, both will work well.
For normal websites, with no big traffic, engine is not that important. Speed issues comes into play if you have a lot of traffic on your website. In that case, InnoDB for some tables is a good idea if those tables need a lot of updating. Typically, such tables can be wp_options, wp_usermeta, wp_comments tables. Also, if you have some custom tables (used for visits tracking or other logging methods), these tables should be InnoDB tables considering that writing with InnoDB is row locked allowing for much more threads to use the table than with MyISAM. Also you must consider that InnoDB will need more resources than MyISAM, so make sure that you have a lot of memory, or even to use separate server for MySQL only. If you want to use full-text search for some tables, there is no choice, you must use MyISAM.
Best thing to do is to test both engines for different tables over a period (again, consider your server resources), and to find the balance setting some tables with InnoDB and some with MyISAM. Also, make sure to check MySQL settings for both engine types and consult system administrator to make sure that both are configured for best performance (this is very important for InnoDB). finding the right balance between these two engines may gain you extra speed. Using only one engine is a good idea if you have some replication environment where different engines can affect replication performance. In such case InnoDB has proven as a better choice.
I think that WordPress in next major revision (maybe for 3.4 or 3.5) should consider setting engines for each table based on the table primary role. That would need some in-depth testing, but it would be worth having best choice of engines from the get go.
If you need some more in-depth information about engines optimization and engine choice, I recommend that you check following websites:
- MySQL Performance Blog: www.mysqlperformanceblog.com
- MySQL 5.5 Storage Engines: dev.mysql.com/doc/refman/5.5/en/storage-engines.html