Let's create one million posts to see the performance of a Wordpress site.
The purpose of this document is to find out what is the underlying data that gets modified in a wordpress database when a post with an image are created.
- I unziped the Worpress.
- I created a brand new database.
- I installed Wordpress.
At that point, I created a database backup pristine_wp.sql.
- I logged in.
- I uploaded an image (
i-am-a-splash-image.png) to media, and filled it's "alt text". - I created a tag for the post
debugtag. - I created a post with it's "title", "content", "excerpt", an image in its content, a featured image, a category (uncategorized) and a tag.
- I published the post.
- I logged out.
At that point, I created another database backup: first_post_wp.sql.
The system I used to test this was:
- OS: MacOS Sierra 10.12.4
- PHP Version: 7.1.1
- Worpress Version: 4.7.3
- Database type: MySQL
The date when this operation was performed was April 7th, 2017.
A total of 5 entries in the wp_postmeta were created.
The first one is an edit lock. It might not be important.
{
"meta_id": 3,
"post_id": 4,
"meta_key": "_edit_lock",
"meta_value": "1491615951:1" // It seems to be the unix epocth of the lock : userId.
}The second one relates a file (the image that I uploaded) to the post of type attachment that was created to hold the image.
{
"meta_id": 4,
"post_id": 5, // ID of the post of type `attachment` that holds the image.
"meta_key": "_wp_attached_file", // Indicates that this post has an attached file.
"meta_value": "2017/04/i-am-a-splash-image.png" // Path to the file.
}The third one is all the metadata of the image I uploaded. That metadata contains information relating it's condition as an attachment.
{
"meta_id": 5,
"post_id": 5,
"meta_key": "_wp_attachment_metadata",
"meta_value": `a:5:{s:5:"width";i:1717;s:6:"height";i:1040;s:4:"file";s:31:"2017/04/i-am-a-splash-image.png";s:5:"sizes";a:5:{s:9:"thumbnail";a:4:{s:4:"file";s:31:"i-am-a-splash-image-150x150.png";s:5:"width";i:150;s:6:"height";i:150;s:9:"mime-type";s:9:"image/png";}s:6:"medium";a:4:{s:4:"file";s:31:"i-am-a-splash-image-300x182.png";s:5:"width";i:300;s:6:"height";i:182;s:9:"mime-type";s:9:"image/png";}s:12:"medium_large";a:4:{s:4:"file";s:31:"i-am-a-splash-image-768x465.png";s:5:"width";i:768;s:6:"height";i:465;s:9:"mime-type";s:9:"image/png";}s:5:"large";a:4:{s:4:"file";s:32:"i-am-a-splash-image-1024x620.png";s:5:"width";i:1024;s:6:"height";i:620;s:9:"mime-type";s:9:"image/png";}s:32:"twentyseventeen-thumbnail-avatar";a:4:{s:4:"file";s:31:"i-am-a-splash-image-100x100.png";s:5:"width";i:100;s:6:"height";i:100;s:9:"mime-type";s:9:"image/png";}}s:10:"image_meta";a:12:{s:8:"aperture";s:1:"0";s:6:"credit";s:0:"";s:6:"camera";s:0:"";s:7:"caption";s:0:"";s:17:"created_timestamp";s:1:"0";s:9:"copyright";s:0:"";s:12:"focal_length";s:1:"0";s:3:"iso";s:1:"0";s:13:"shutter_speed";s:1:"0";s:5:"title";s:0:"";s:11:"orientation";s:1:"0";s:8:"keywords";a:0:{}}}`
}Let's unserialize the meta_value of the previous bunch of data:
{
"width": 1717,
"height": 1040,
"file": "2017/04/i-am-a-splash-image.png",
"sizes": {
"thumbnail": {
"file": "i-am-a-splash-image-150x150.png",
"width": 150,
"height": 150,
"mime-type": "image/png"
},
"medium": {
"file": "i-am-a-splash-image-300x182.png",
"width": 300,
"height": 182,
"mime-type": "image/png"
},
"medium_large": {
"file": "i-am-a-splash-image-768x465.png",
"width": 768,
"height": 465,
"mime-type": "image/png"
},
"large": {
"file": "i-am-a-splash-image-1024x620.png",
"width": 1024,
"height": 620,
"mime-type": "image/png"
},
"twentyseventeen-thumbnail-avatar": {
"file": "i-am-a-splash-image-100x100.png",
"width": 100,
"height": 100,
"mime-type": "image/png"
}
},
"image_meta": {
"aperture": "0",
"credit": "",
"camera": "",
"caption": "",
"created_timestamp": "0",
"copyright": "",
"focal_length": "0",
"iso": "0",
"shutter_speed": "0",
"title": "",
"orientation": "0",
"keywords": []
}
}The fourth entry relates the post of the image to it's "alt text".
{
"meta_id": 6,
"post_id": 5, // ID of the post of type `attachment` that holds the image.
"meta_key": "_wp_attachment_image_alt", // Indicates that this metadata holds the alt text of the image.
"meta_value": "I am an alt text." // Alt text of the image.
}The fifth entry relates the entry of the very post to it's thumbnail id.
{
"meta_id": 7,
"post_id": 4, // ID of the very post.
"meta_key": "_thumbnail_id", // Indicates that this metadata relates the very post to it's thumbnail.
"meta_value": "5" // ID of the post of the image.
}A total of 4 entries in the wp_posts table were created.
The first is an Auto Draft with post_type post. It might not be imporant.
The second one is the important one, the entry of the very post.
{
ID: 4,
post_author: 1,
post_date: '2017-04-08 01:47:04',
post_date_gmt: '2017-04-08 01:47:04',
post_content: `I am just a simple content.
[caption id="attachment_5" align="alignnone" width="300"]<img class="size-medium wp-image-5" src="http://localhost/razon/wp-content/uploads/2017/04/i-am-a-splash-image-300x182.png" alt="I am an alt text." width="300" height="182" /> I am an image caption.[/caption]`,
post_title: 'I am a debug title',
post_excerpt: 'I am a simple excerpt.',
post_status: 'publish',
comment_status: 'open',
ping_status: 'open',
post_password: '',
post_name: 'i-am-a-debug-title',
to_ping: '',
pinged: '',
post_modified: '2017-04-08 01:47:04',
post_modified_gmt: '2017-04-08 01:47:04',
post_content_filtered: '',
post_parent: 0,
guid: 'http://localhost/razon/?p=4',
menu_order: 0,
post_type: 'post',
post_mime_type: '',
comment_count: 0,
}The thirth one is the one for the image.
{
ID: 5,
post_author: 1,
post_date: "2017-04-08 01:45:00",
post_date_gmt: "2017-04-08 01:45:00",
post_content: "I am a description.",
post_title: "i-am-a-splash-image",
post_excerpt: "I am an image caption.",
post_status: "inherit",
comment_status: "open",
ping_status: "closed",
post_password: "",
post_name: "i-am-a-splash-image",
to_ping: "",
pinged: "",
post_modified: "2017-04-08 01:45:28",
post_modified_gmt: "2017-04-08 01:45:28",
post_content_filtered: "",
post_parent: 4,
guid: "http://localhost/razon/wp-content/uploads/2017/04/i-am-a-splash-image.png",
menu_order: 0,
post_type: "attachment",
post_mime_type: "image/png",
comment_count: 0
}The fourth one is a Revition with post_type revition. It might not be imporant.
The first entry in wp_terms_relationships relates the very post to the term "uncategorized" under the taxonomy "category".
{
object_id: 4, // This is the ID of the post.
term_taxonomy_id: 1, // This is the ID of the default term, "uncategorized" under the taxonomy "category"
term_order: 0,
}The second entry in wp_terms_relationships relates the very post to the term "debugtag" under the taxonomy "post_tag".
{
object_id: 4, // This is the ID of the post.
term_taxonomy_id: 2, // This is for the term I created, "debugtag" under the taxonomy "post_tag".
term_order: 0,
}When I created a tag with name debugtag, this entry was created in wp_term_taxonomy.
{
term_taxonomy_id: 2,
term_id: 2,
taxonomy: 'post_tag',
description: '',
parent: 0,
count: 1,
}That seems to be a built-in type of taxonomy which all the tags of a post have.
When I created a tag with name debugtag, this entry was created in wp_terms.
{
term_id: 2,
name: 'debugtag',
slug: 'debugtag',
term_group: 0,
}That seems to be the actual tag I created.
Entries with meta_key 'wp_dashboard_quick_press_last_post_id', 'closedpostboxes_post', 'metaboxhidden_post', 'wp_user-settings', 'wp_user-settings-time' were created. They seem to be not important.
Also, the entry with meta_key 'session_tokens' was deleted. It might be due to the fact I logged out when I created the post.
The images were created in this directory wp-content/uploads/2017/04.
The image files were:
i-am-a-splash-image-100x100.pngi-am-a-splash-image-1024x620.pngi-am-a-splash-image-150x150.pngi-am-a-splash-image-300x182.pngi-am-a-splash-image-768x465.pngi-am-a-splash-image.png
To create one million posts, I can start from the first_post_wp.sql database, that contains an uploaded image, and a post. I will no longer need to upload images or create more post types of type attachment, or terms, or taxonomies.
The thing I want to create is:
- A post of post type `post`.
- That post must have title, content, excerpt.
- The content of the post must have an attachment (the same as the debug post).
- That post must have a tag. I will use the `debugtag` i previously created.
- That post must have a category. I will use the `uncategorized` default category of Wordpress.
So, the tables I will have to insert data into are in this order:
{
// ID: 4, // This must be ommited so MySQL can assign an ID automatically.
post_author: 1, // Keep it constant to assign it to my usrer ID.
post_date: '2017-04-08 01:47:04', // Keep it constant. We don't want to mess witih dates.
post_date_gmt: '2017-04-08 01:47:04', // Keep it constant. We don't want to mess witih dates.
// In the content add the ${$new_post_id} to differentiate it.
post_content: `I am just a simple content x${$new_post_id}.
[caption id="attachment_5" align="alignnone" width="300"]<img class="size-medium wp-image-5" src="http://localhost/razon/wp-content/uploads/2017/04/i-am-a-splash-image-300x182.png" alt="I am an alt text." width="300" height="182" /> I am an image caption.[/caption]`,
// In the title of the post add the ${$new_post_id} to differentiate it.
post_title: `I am a debug title x${$new_post_id}`,
// In the excerpt add the ${$new_post_id} to diffetentiate it.
post_excerpt: `I am a simple excerpt x${$new_post_id}.`,
post_status: 'publish', // Keep it constant. We want all our posts to be published.
comment_status: 'open', // Keep it.
ping_status: 'open', // Keep it.
post_password: '', // Keep it.
// The post name MUST be unique. To ensure this, we add the `x-${$new_post_id}` at the end of the value.
post_name: `i-am-a-debug-title-x-${$new_post_id}`,
to_ping: '', // Keep it.
pinged: '', // Keep it.
post_modified: '2017-04-08 01:47:04', // Keep it.
post_modified_gmt: '2017-04-08 01:47:04', // Keep it.
post_content_filtered: '', // Keep it.
post_parent: 0, // Keep it.
// The guid MUST be unique, and also, the 'p' param must be the ID of the post. To achieve this, we use our friend ${$new_post_id}.
guid: `http://localhost/razon/?p=${$new_post_id}`,
menu_order: 0, // Keep it.
post_type: 'post', // Keep it.
post_mime_type: '', // Keep it.
comment_count: 0, // Keep it.
}To relate the post to its thumbnail:
{
// "meta_id": 7, // This must be ommited so MySQL can assign an ID automatically.
"post_id": $new_post_id, // ID of the recently created post.
"meta_key": "_thumbnail_id", // Keep this constant. Indicates that this metadata relates the very post to it's thumbnail.
"meta_value": "5" // Keep this constant. ID of the post of the image, the only one I have uploaded.
}Relate the new post to the term uncategorized under the taxonomy category.
{
object_id: $new_post_id, // ID of the new post.
term_taxonomy_id: 1, // ID of the default term, "uncategorized" under the taxonomy "category"
term_order: 0,
}Relate the new post to the term debugtag under the taxonomy post_tag.
{
object_id: $new_post_id, // ID of the new post.
term_taxonomy_id: 2, // This is for the term I created, "debugtag" under the taxonomy "post_tag".
term_order: 0,
}drop procedure if exists load_foo_test_data;
delimiter #
create procedure load_foo_test_data()
begin
declare v_max int unsigned default 1; -- Alter this number up to one million, be careful not to overload your database, it might die.
declare v_counter int unsigned default 0;
start transaction;
while v_counter < v_max do
-- CREATE POST ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------
INSERT INTO `wp_posts` (
-- `ID`, // This must be ommited so MySQL can assign an ID automatically.
`post_author`,
`post_date`,
`post_date_gmt`,
`post_content`,
`post_title`,
`post_excerpt`,
`post_status`,
`comment_status`,
`ping_status`,
`post_password`,
`post_name`,
`to_ping`,
`pinged`,
`post_modified`,
`post_modified_gmt`,
`post_content_filtered`,
`post_parent`,
`guid`,
`menu_order`,
`post_type`,
`post_mime_type`,
`comment_count`
) VALUES (
1, -- post_author // Keep it constant to assign it to my usrer ID.
'2017-04-08 01:47:04', -- post_date // Keep it constant. We don't want to mess witih dates.
'2017-04-08 01:47:04', -- post_date_gmt // Keep it constant. We don't want to mess witih dates.
'', -- post_content // Updated later on.
'', -- post_title // Updated later on.
'', -- post_excerpt // Updated later on.
'publish', -- post_status // Keep it constant. We want all our posts to be published.
'open', -- comment_status // Keep it.
'open', -- ping_status // Keep it.
'', -- post_password // Keep it.
'', -- post_name // Updated later on.
'', -- to_ping // Keep it.
'', -- pinged // Keep it.
'2017-04-08 01:47:04', -- post_modified // Keep it.
'2017-04-08 01:47:04', -- post_modified_gmt // Keep it.
'', -- post_content_filtered // Keep it.
0, -- post_parent // Keep it.
'', -- guid // Updated later on.
0, -- menu_order // Keep it.
'post', -- post_type // Keep it.
'', -- post_mime_type // Keep it.
1 -- comment_count // Keep it.
);
set @future_id = LAST_INSERT_ID();
UPDATE `wp_posts` SET
-- In the content add the ${$new_post_id} to differentiate it.
`post_content` = CONCAT('I am just a simple content x-', @future_id, '.
[caption id="attachment_5" align="alignnone" width="300"]<img class="size-medium wp-image-5" src="http://localhost/wp-content/uploads/2017/04/i-am-a-splash-image-300x182.png" alt="I am an alt text." width="300" height="182" /> I am an image caption.[/caption]'),
-- In the title of the post add the ${$new_post_id} to differentiate it.
`post_title` = CONCAT('I am a debug title x-', @future_id),
-- In the excerpt add the ${$new_post_id} to diffetentiate it.
`post_excerpt` = CONCAT('I am a simple excerpt x-', @future_id),
-- The post name MUST be unique. To ensure this, we add the `x-${$new_post_id}` at the end of the value.
`post_name` = CONCAT('i-am-a-debug-title-x-', @future_id),
-- The guid MUST be unique, and also, the 'p' param must be the ID of the post. To achieve this, we use our friend ${$new_post_id}.
`guid` = CONCAT('http://localhost/?p=', @future_id)
WHERE `ID` = @future_id
;
-- UPDATE WP_POST_META ----------------------------------------------------------------------
-- ------------------------------------------------------------------------------------------
-- These two guys were added when I created the post programatically with PHP, but not when I created the post using the normal UI tools of the Worpress admin. So, I won't use them.
INSERT INTO `wp_postmeta` (
-- `meta_id`, // This must be ommited so MySQL can assign an ID automatically.
`post_id`,
`meta_key`,
`meta_value`
) VALUES (
@future_id, -- post_id // ID of the recently created post.
'_thumbnail_id', -- meta_key // Keep this constant. Indicates that this metadata relates the very post to it's thumbnail.
'5' -- meta_value // Keep this constant. ID of the post of the image, the only one I have uploaded.
);
-- INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (@future_id, '_pingme', '1');
-- INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES (@future_id, '_encloseme', '1');
-- UPDATE WP_TERM_RELATIONSHIPS -------------------------------------------------------------
-- ------------------------------------------------------------------------------------------
-- Relate the new post to the term `uncategorized` under the taxonomy `category`.
-- object_id: $new_post_id, // ID of the new post.
-- term_taxonomy_id: 1, // ID of the default term, "uncategorized" under the taxonomy "category"
-- term_order: 0,
INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (@future_id, 1, 0);
-- Relate the new post to the term `debugtag` under the taxonomy `post_tag`.
-- object_id: $new_post_id, // ID of the new post.
-- term_taxonomy_id: 2, // This is for the term I created, "debugtag" under the taxonomy "post_tag".
-- term_order: 0,
INSERT INTO `wp_term_relationships` (`object_id`, `term_taxonomy_id`, `term_order`) VALUES (@future_id, 2, 0);
-- UPDATE COUNTER ---------------------------------------------------------------------------
set v_counter = v_counter + 1;
end while;
commit;
end #
delimiter ;
call load_foo_test_data();