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:

wp_postmeta

mysql> show create table wp_postmetaG
*************************** 1. row ***************************
       Table: wp_postmeta
Create Table: CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_increment,
  `post_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext,
  PRIMARY KEY  (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM AUTO_INCREMENT=2926 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

wp_posts

mysql> show create table wp_postsG
*************************** 1. row ***************************
       Table: wp_posts
Create Table: CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `post_author` bigint(20) unsigned NOT NULL default '0',
  `post_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL default 'publish',
  `comment_status` varchar(20) NOT NULL default 'open',
  `ping_status` varchar(20) NOT NULL default 'open',
  `post_password` varchar(20) NOT NULL default '',
  `post_name` varchar(200) NOT NULL default '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL default '0',
  `guid` varchar(255) NOT NULL default '',
  `menu_order` int(11) NOT NULL default '0',
  `post_type` varchar(20) NOT NULL default 'post',
  `post_mime_type` varchar(100) NOT NULL default '',
  `comment_count` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`ID`), KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`), KEY `post_author` (`post_author`)
) ENGINE=MyISAM AUTO_INCREMENT=59921 DEFAULT CHARSET=utf8

This may sound rather ugly in the database world, but what is needed would be a Cartesian Product that would be manifested as a regular table.

Brace yourself, this is going to be rather disturbing…

DROP TABLE IF EXISTS wp_posts_postmeta_joined;
CREATE TABLE wp_posts_postmeta_joined SELECT * FROM wp_posts WHERE 1 = 2;
ALTER TABLE wp_posts_postmeta_joined
    ADD COLUMN `meta_key` varchar(255) default NULL,
    ADD COLUMN `meta_value` longtext
;
ALTER TABLE wp_posts_postmeta_joined ENGINE=MyISAM;
ALTER TABLE wp_posts_postmeta_joined ROW_FORMAT=Fixed;
ALTER TABLE wp_posts_postmeta_joined
      ADD INDEX status_ndx (`ID`,`status`),
      ADD INDEX `post_name` (`ID`,`post_name`),
      ADD INDEX `type_status_date` (`ID`,`post_type`,`post_status`,`post_date`),
      ADD INDEX `post_parent` (`ID`,`post_parent`),
      ADD INDEX `post_author` (`ID`,`post_author`),
      ADD INDEX `id_meta_key`(`ID`,`meta_key`)
;
ALTER TABLE wp_posts_postmeta_joined DISABLE KEYS;
INSERT INTO wp_posts_postmeta_joined 
    SELECT A.*,B.meta_key,B.meta_value
    FROM wp_posts A LEFT JOIN wp_postmeta B
    ON A.ID = B.post_id
;
ALTER TABLE wp_posts_postmeta_joined ENABLE KEYS;

Basically, everything and the kitchen sink is in this table. You would use this table to run your SELECT queries. Any query not perform well? Just add the needed index into the script and rebuild it.

CAVEAT #1

This process would be worthwhile as long as the table can be rebuilt during off-hours and made available before the business day begins. You can make the old copy available during the rebuild process by running like this:

DROP TABLE IF EXISTS wp_posts_postmeta_joined_new;
CREATE TABLE wp_posts_postmeta_joined_new SELECT * FROM wp_posts WHERE 1 = 2;
ALTER TABLE wp_posts_postmeta_joined_new 
    ADD COLUMN `meta_key` varchar(255) default NULL,
    ADD COLUMN `meta_value` longtext
;
ALTER TABLE wp_posts_postmeta_joined_new ENGINE=MyISAM;
ALTER TABLE wp_posts_postmeta_joined_new ROW_FORMAT=Fixed;
ALTER TABLE wp_posts_postmeta_joined_new 
      ADD INDEX status_ndx (`ID`,`status`),
      ADD INDEX `post_name` (`ID`,`post_name`),
      ADD INDEX `type_status_date` (`ID`,`post_type`,`post_status`,`post_date`),
      ADD INDEX `post_parent` (`ID`,`post_parent`),
      ADD INDEX `post_author` (`ID`,`post_author`),
      ADD INDEX `id_meta_key`(`ID`,`meta_key`)
;
ALTER TABLE wp_posts_postmeta_joined_new DISABLE KEYS;
INSERT INTO wp_posts_postmeta_joined_new 
    SELECT A.*,B.meta_key,B.meta_value
    FROM wp_posts A LEFT JOIN wp_postmeta B
    ON A.ID = B.post_id
;
ALTER TABLE wp_posts_postmeta_joined_new ENABLE KEYS;
ALTER TABLE wp_posts_postmeta_joined RENAME wp_posts_postmeta_joined_old;
ALTER TABLE wp_posts_postmeta_joined_new RENAME wp_posts_postmeta_joined;
DROP TABLE wp_posts_postmeta_joined_old;

CAVEAT #2

The table wp_posts_postmeta_joined should be MyISAM regardless of the storage engines used by wp_posts and wp_postmeta.

I added ROW_FORMAT=Fixed to increase the table read speed 20-25%. This table can be rather large, so make sure you have the diskspace. I wrote about this back on Mar 25, 2011.

Give it a Try !!!

Large database causes slow load