WordPress is designed to run only on MySQL database. For that reason,
optimizing MySQL is a very important issue if you want to run WordPress
very fast on the hardware you use. But, that might not be as
straightforward as you may think, considering that MySQL can offer you
many storage engines.
Since MySQL 5.0, there are 10 (yes, ten) storage engines in MySQL.
Before MySQL 5.5 was released, MyISAM was a 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, the default engine is now
InnoDB. Great thing about MySQL is that you can use different storage
engines for each table.
This is the 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 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
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 the following websites:
- MySQL Performance Blog: www.mysqlperformanceblog.com
- MySQL 5.5 Storage Engines: dev.mysql.com/doc/refman/5.5/en/storage-engines.html