CODEWITHSUNDEEP
phpmysqlwordpressquery-optimizationjson-api
Vinit Sinkar
Vinit Sinkar

Reputation: 99

Response Time of Json API is slow

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__.'\request' );

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.

Upvotes: 1

Views: 1159

Answers (1)

Rick James
Rick James

Reputation: 142208

Get rid of the id in postmeta and change the PK to PRIMARY KEY(post_id, meta_key).

See of the GROUP BY can be removed without changing the output.

What does JSON have to do with it?

Upvotes: 1

Related Questions