I figured out one of the major query cache problems. The reason why the query cache doesn’t work with many of these expensive slow queries is because the views are being updated constantly and breaking the query cache with Update statements on recording the views. I see hundreds of updates like this.
UPDATE `wp_ap_qameta` SET `ptype` = ‘question’, `views` = 1 WHERE `post_id` = 10153
Can I just prevent the views from being updated with a hook and how do I do that?
Otherwise all these slow queries keep missing the cache.
SELECT wp_posts.*, qameta.*, qameta.votes_up – qameta.votes_down AS votes_net FROM wp_posts LEFT JOIN wp_ap_qameta qameta ON qameta.post_id = wp_posts.ID WHERE 1=1 AND wp_posts.post_type = ‘question’ AND ((wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘trash’ OR wp_posts.post_status = ‘moderate’ OR wp_posts.post_status = ‘private_post’) OR ( wp_posts.post_author = 1 AND wp_posts.post_status IN (‘publish’, ‘private_post’, ‘trash’, ‘moderate’) ) ) GROUP BY wp_posts.ID ORDER BY CASE WHEN IFNULL(qameta.featured, 0) =1 THEN 1 ELSE 2 END ASC, qameta.last_updated DESC LIMIT 0, 20 10
SELECT count(wp_posts.ID) FROM wp_posts LEFT JOIN wp_ap_qameta qameta ON qameta.post_id = wp_posts.ID WHERE 1=1 AND wp_posts.post_type = ‘question’ AND ((wp_posts.post_status = ‘publish’)) 9
SELECT count(wp_posts.ID) FROM wp_posts LEFT JOIN wp_ap_qameta qameta ON qameta.post_id = wp_posts.ID WHERE 1=1 AND wp_posts.post_type = ‘question’ AND ((wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘trash’ OR wp_posts.post_status = ‘moderate’ OR wp_posts.post_status = ‘private_post’) OR ( wp_posts.post_author = 1 AND wp_posts.post_status IN (‘publish’, ‘private_post’, ‘trash’, ‘moderate’) ) ) 9
SELECT wp_posts.*, qameta.*, qameta.votes_up – qameta.votes_down AS votes_net FROM wp_posts LEFT JOIN wp_ap_qameta qameta ON qameta.post_id = wp_posts.ID WHERE 1=1 AND wp_posts.post_type = ‘question’ AND ((wp_posts.post_status = ‘publish’)) GROUP BY wp_posts.ID ORDER BY CASE WHEN IFNULL(qameta.featured, 0) =1 THEN 1 ELSE 2 END ASC, qameta.last_updated DESC LIMIT 0, 20 9
SELECT SQL_CALC_FOUND_ROWS wp_posts.*, qameta.*, qameta.votes_up – qameta.votes_down AS votes_net FROM wp_posts LEFT JOIN wp_ap_qameta qameta ON qameta.post_id = wp_posts.ID WHERE 1=1 AND wp_posts.post_type = ‘question’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘moderate’ OR wp_posts.post_status = ‘private_post’ OR wp_posts.post_status = ‘future’ OR wp_posts.post_status = ‘draft’ OR wp_posts.post_status = ‘pending’ OR wp_posts.post_status = ‘private’) ORDER BY wp_posts.post_date DESC LIMIT 0, 20 1
Upgraded one live site and got:
[11-Feb-2017 16:41:59 UTC] WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘post_type IN (‘question’, ‘answer’) LIMIT 0,50′ at line 1 for query SELECT SQL_CALC_FOUND_ROWS ID, post_type, post_parent, post_status FROM wp_posts LEFT JOIN wp_ap_qameta ON post_id = ID WHERE ptype IS NULL post_type IN (‘question’, ‘answer’) LIMIT 0,50 made by do_action(‘wp_ajax_ap_migrator_4x’), WP_Hook->do_action, WP_Hook->apply_filters, AnsPress_Admin_Ajax::ap_migrator_4x, AP_Update_Helper->__construct, AP_Update_Helper->migrate_post_data
Rahul. Here they are along with row numbers and times.
SELECT count(*) as count, p.post_status
FROM wp_posts p
INNER JOIN wp_ap_qameta qameta
ON p.ID = qameta.post_id
WHERE p.post_status NOT IN (‘trash’, ‘draft’)
AND p.post_type = ‘question’
AND qameta.flags > 0
GROUP BY p.post_status
ap_total_posts_count()
wp-content/plugins/anspress/includes/functions.php:748
+ Plugin: anspress 0 2.0631
SELECT count(*) as count, p.post_status
FROM wp_posts p
INNER JOIN wp_ap_qameta qameta
ON p.ID = qameta.post_id
WHERE p.post_status NOT IN (‘trash’, ‘draft’)
AND p.post_type = ‘answer’
AND qameta.flags > 0
GROUP BY p.post_status
ap_total_posts_count()
wp-content/plugins/anspress/includes/functions.php:748
+ Plugin: anspress 0 2.0062
SELECT post_status, COUNT( * ) AS num_posts
FROM wp_posts
WHERE post_type = ‘question’
GROUP BY post_status
wp_count_posts()
wp-includes/post.php:2213
+ Plugin: anspress 1 5.0544
SELECT post_status, COUNT( * ) AS num_posts
FROM wp_posts
WHERE post_type = ‘answer’
GROUP BY post_status
wp_count_posts()
wp-includes/post.php:2213
+ Plugin: anspress 1 4.4439
I’m stumped, I know I can add a custom reputation option via Anspress > Reputation.
But how do I use this in a function?
I’ve already got a button hooked up to ajax, so a function is called on click. I just need to have it trigger the action/hook/filter to register the extra reputation but despite trawling through the code, docs, support I can’t find anything to work out what’s required to connect the custom rep event and the code I have.
Any suggestions or examples would be very, very helpful.