DISCLAIMER : Not a WordPress Developer, Just a MySQL DBA
There is a special table structure in Oracle called a Materialized View. Basically, it is built by performing a JOIN query (using no WHERE clause) and storing the result set. Then, simply SELECT from that static result set rather than rebuilding each JOIN result.
First, let's look at the two tables:
If the code creating new tables uses dbDelta() (it should), you can filter the query (see wp-admin/includes/upgrade.php):
add_filter( 'dbdelta_create_queries', function( Array $queries )
foreach ( $queries as $table => $query )
if ( FALSE !== stripos( $query, 'CREATE TABLE' ) )
// change the query here
I can't comment on foreign keys but moving to InnoDB would be a good idea for large scale.
Although it's a little slower than MyISAM, it does row level locking, rather than table level locking. As a result saving a post or doing a lengthy operation or query won't lock the posts table, freezing the entire site while it waits for the table to unlock.
There's quite a lot of information on here about switching to InnoDB
There are a number of things to think about:
InnoDB is helpful with you're faced with contention - ie when you have tables that are being written to as well as being read
InnoDB does not support FULLTEXT indexes so plugins that rely on ...
I recently played around with an Ubuntu LAMP stack and a WordPress install and looked at changing to InnoDB or NGINX.
Just to set the scene, I had Next Gen Gallery plugin running and I noticed it's tables were 'different'. So I thought to investigate changing to InnoDB.
Can I use InnoDB without having an adverse effect on my WordPress installation?
It is possible with MySQL, and it doesn't seem to create any problem with wordpress's dbDelta()
Here is the line you need to change
$charset_collate = $wpdb->get_charset_collate() . ' engine = innoDB';
You simply have to add engine = innoDB at the end of the SQL query and the correct engine will be used by MySQL
Commenting out the line
innodb_force_recovery = 1
in /etc/my.cnf thus:
# innodb_force_recovery = 1
made the mysql innodb tables in the database accessible. Apparently this setting causes innodb to become read-only. I hope this helps someone in the future. If you don't have access to /etc/my.cnf on shared hosting, ask your host to fix it for you. When it'...
It matters when it comes to backing up your Wordpress site. I learned this the hard way. If the DB is innodb then R1 cloud restore type backups won't save all the data because it has to be shut down first in order to back it up. Whereas MYISAM can be backed up no problem.