We are using the JSON API plugin in which we are facing issue for the performance of the App.It takes a 10-20 seconds of the load time for the startup of the App.
We tested the Api request and we got the result as some slow sql queries gets call as mentioned below.This query gets originating from the JSON API request.
We are unable to identify from where does this sql query gets triggered as the path mentioned in the query we made some changes in post_type for example we excluded the post1 AND post2 from query.php but stiil no change we are getting the same result in slow sql query in database.
SELECT iqoyau1_posts.ID
FROM iqoyau1_posts
INNER JOIN iqoyau1_postmeta ON ( iqoyau1_posts.ID = iqoyau1_postmeta.post_id )
WHERE 1=1
AND (
( iqoyau1_postmeta.meta_key = '_hide-in-ipad'
AND CAST(iqoyau1_postmeta.meta_value AS CHAR) = '1'
)
)
AND iqoyau1_posts.post_type IN ('post1', 'post2', 'post3', 'post4',
'post5)
AND ((iqoyau1_posts.post_status = 'publish'))
GROUP BY iqoyau1_posts.ID
ORDER BY iqoyau1_posts.post_date DESC /* in [/plugins/ipad-app/query.php:38]
Here is our code of query.php:
<?php
function pre_get_posts( $wp_query ){
$json = $wp_query->get( 'json' );
if( !$json )
return $wp_query;
$api_version = $wp_query->get( 'api_ver' );
if( version_compare($api_version, '2.0', '<') ){
$wp_query->query_vars['post_type'] =array( 'ipad-post' );
$wp_query->query_vars['posts_per_page'] = 36;
}
elseif( version_compare($api_version, '2.0', '>=') ){
$url = $_SERVER['REQUEST_URI'];
if ($_GET["post_type"]== "XXX" && strpos($url,'api_v2') ){
$wp_query->query_vars['post_type'] = 'XXX';
$wp_query->query_vars['posts_per_page'] = $_GET["count"];
}else{
$wp_query->query_vars['post_type'] = array( 'post3', 'post4','post5);
$wp_query->query_vars['posts_per_page'] = $_GET["count"];
}
// exclude posts set to not show in ipad app
$not_in = new \WP_Query( array(
'fields' => 'ids',
'meta_key' => '_hide-in-ipad',
'meta_value' => 1,
'posts_per_page' => -1
) );
$wp_query->query_vars['post__not_in'] =
array_merge( $not_in->posts, $wp_query->get('post__not_in') );
}
$wp_query->query_vars['ignore_sticky_posts'] = TRUE;
return $wp_query;
}
add_filter( 'pre_get_posts', __NAMESPACE__.'\pre_get_posts' );
/*
* allow post__not_in in query string with normal or json syntax
* @param array
* @return array
*/
function request( $qv ){
if( !empty($qv['api_ver'])
&& version_compare($qv['api_ver'], '2.0', '>=')
&& !empty($_GET['post__not_in']) ){
if( is_string($_GET['post__not_in']) )
$qv['post__not_in'] = (array) json_decode( $_GET['post__not_in'] );
elseif( is_array($_GET['post__not_in']) )
$qv['post__not_in'] = $_GET['post__not_in'];
$qv['post__not_in'] = array_map( 'intval', $qv['post__not_in'] );
}
return $qv;
}
add_filter( 'request', __NAMESPACE__.'equest' );
We have also optimized the attachments and tags from the json response the performance has improved little bit as before but still we are facing the performance issue from slow database query. How can we optimize the query Any Help.
