I am creating my own WordPress plugin that imports profiles as posts,and while the script works, I am looking to improve the query that inserts/updates the posts.
This script runs at every 5 minutes and works like this,
1.import/update all profiles from an external source in local users table. This is straightforward and works ok
2. Set all existing posts custom field status to offline before updating
$offline = "UPDATE $meta SET meta_value='offline' WHERE meta_key='status'";
3. Select from table users all profiles that are online
$new_query = "SELECT * FROM users WHERE status='online' ORDER BY visitors DESC";
4. Foreach profile, if post exists,update, else create new post
$result = $wpdb->get_results($new_query);
foreach ($result as $post){
$cat = $post->gender.'s';
$getterm = get_term_by('name',$cat,'user-category');
$cat_id = $getterm->term_id;
$custfields = array (
'name' => $post->name,
'age' => $post->age,
'subject' => $post->subject,
'status' => $post->status,
'fans' => $post->fans,
'visitors' => $post->visitors,
);
endif;
$postarr = array (
'post_title' => $post->username,
'post_name' => $post->username.'-profile',
'post_content' => 'something',
'post_type' => 'users-profile',
'post_category' => array($cat_id),
'post_status' => 'publish',
'meta_input' => $custfields
);
$postcheck = get_page_by_title($post->username,OBJECT, 'users-profile');
if($postcheck):
update_post_meta($postcheck->ID ,'subject', $post->subject );
update_post_meta($postcheck->ID ,'status', $post->status );
update_post_meta($postcheck->ID ,'visitors', $post->visitors );
else :
wp_set_object_terms(wp_insert_post( $postarr,true),$cat_id,'user-category',true);
endif;
}
SHOW CREATE TABLE wp_users
wp_users CREATE TABLE `wp_users` (
`username` varchar(111) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`name` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`age` int(11) NOT NULL,
`gender` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`subject` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`languages` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`isnew` tinyint(1) NOT NULL,
`imagebig` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`fans` int(11) NOT NULL,
`visitors` int(11) NOT NULL,
`timeonline` int(11) NOT NULL,
`status` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`location` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
SHOW CREATE TABLE `wp_postmeta`
wp_postmeta 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) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=405678 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
SHOW CREATE TABLE `wp_posts`
wp_posts 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 COLLATE utf8mb4_unicode_ci NOT NULL,
`post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`post_password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
`pinged` text COLLATE utf8mb4_unicode_ci 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` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
`guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT 0,
`post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`(191)),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=57991 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
What i am trying to do is, move the offline switch after the update/insert and apply it to all posts not in the SELECT statement...and maybe a better way of doing the update/insert.
Maybe something like this:
Select all online profiles from users table, select posts that matches the profiles (post title = profile username), if post exist update else create, switch offline the rest of the posts in table. Is this possible?
Prefix indexing (KEY meta_key (meta_key(191))) is an abomination. Yes, it is a 'necessary' kludge due to utf8mb4. But it is hurting you. Do this
SELECT MAX(CHAR_LENGTH(meta_key)) from wp_postmeta;
If it comes back 191 or less, then change the size of meta_key to 191 and get rid of (191) in the index. And see the other tips in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
Those tips may be the solution to your Question.
UPDATEing a flag in lots of rows seems like a clumsy way to process things. Isn't there a way to simply fold in the new data without the using the offline/online flag? (I guess I am still missing the intent.) If it is a million rows, that update may take a long time.
"all posts not in the SELECT" -- This sounds like a "multi-table UPDATE" statement. It would involve a LEFT JOIN ON ... WHERE ... IS NULL. Can you mock up a few rows with the important columns? Show us the "before" and "after" of what should be in those tables.
You could also apply the above action to post_name.
ageint(11)
Do you run around incrementing that every year?