slow-queries with many posts. what is this sql SELECT doing?

  • varange

    (@varange)


    11 years, 6 months ago

    Hi all, I get heaps of slow-query logs (set to 2s at the moment) from this type of query:

    SELECT l.ID, post_title, post_content, post_name, post_author, post_parent, post_modified_gmt, post_date, post_date_gmt
FROM (

SELECT ID
FROM wp_posts
WHERE post_status =  'publish'
AND post_password =  ''
AND post_type =  'post'
AND post_author !=0
AND post_date !=  '0000-00-00 00:00:00'
ORDER BY post_modified ASC
LIMIT 100
OFFSET 108700
)o
JOIN wp_posts l ON l.ID = o.ID
ORDER BY l.ID

    What is this query doing? Is it a related-posts thing? What can I do to speed it up? My wp-posts table has over 300k entries now.

    http://www.fairtattoo.com

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter varange

    (@varange)

    11 years, 6 months ago

    Ah, found it. Yoast wordpress-SEO plugin. I’ll see if I can run that query on a cron-basis, and post what I find..

    Thread Starter varange

    (@varange)

    11 years, 6 months ago

    This query exists in wp-content/plugins/wordpress-seo/inc/class-sitemaps.php on line 707.

    $query = $wpdb->prepare( "SELECT l.ID, post_title, post_content, post_name, post_author, post_parent, ".
                "post_modified_gmt, post_date, post_date_gmt FROM ".
                "( SELECT ID FROM $wpdb->posts {$join_filter} WHERE ".
                "post_status = '%s' AND post_password = '' AND post_type = '%s' AND post_author != 0 ".
                "AND post_date != '0000-00-00 00:00:00' {$where_filter} ".
                "ORDER BY post_modified ASC LIMIT %d OFFSET %d ) o JOIN $wpdb->posts l ON l.ID = o.ID ORDER BY l.ID",
                $status, $post_type, $steps, $offset
            );

    There was already a problem with this query being slow in the past, but Yoast updated it.

    https://wordpress.org/support/topic/plugin-wordpress-seo-by-yoast-performance-suggestion?replies=6
    and
    http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

    I’m stuck now.

    uday.dahale

    (@udaydahale-1)

    10 years, 6 months ago

    Hello admin n all,

    I want optimize below query

    SELECT id, post_date_gmt FROM wp_posts WHERE post_status = ‘publish’ AND post_type
    IN (‘post’, ‘page’, ‘attachment’, ‘xx’, ‘xx’, ‘xx’, ‘xx’, ‘xx’, ‘xx’, ‘xx’) ORDER BY post_date_gmt DESC LIMIT 1

    Please do needful or give me solution for this

    regards,
    UD

    Justin Greer

    (@justingreerbbi)

    10 years, 6 months ago

    @uday.dahale

    Please create your own topic as you will get better support. This thread is 11 months old! :/

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘slow-queries with many posts. what is this sql SELECT doing?’ is closed to new replies.