Support » Plugin: Query Monitor » Slow queries in “All Posts”

  • Resolved Sina Saeedi

    (@melodymag)


    Hi there.
    I have a site with more than 200,000 users.
    “All Posts” and “All Pages” are running very slow on my dedicated server. They take about one minute and they have two very slow queries:

    SELECT wp_users.ID,wp_users.user_login,wp_users.display_name
    FROM wp_users
    INNER JOIN wp_usermeta
    ON ( wp_users.ID = wp_usermeta.user_id )
    WHERE 1=1
    AND ( ( wp_usermeta.meta_key = 'wp_user_level'
    AND wp_usermeta.meta_value != '0' ) )
    ORDER BY display_name ASC

    Is there any way to speed these parts up?

Viewing 11 replies - 1 through 11 (of 11 total)
  • Plugin Author John Blackbourn

    (@johnbillion)

    WordPress Core Developer

    Unfortunately any meta_value query will be the first to get slow when you have a large number of users or posts.

    Is this query coming from a plugin or from WordPress core?

    Thread Starter Sina Saeedi

    (@melodymag)

    It’s from the core.
    Do you believe setting an Index on the meta table may help?
    I read somewhere that changes on DB will be reverted on Core update, is it right?

    Plugin Author John Blackbourn

    (@johnbillion)

    WordPress Core Developer

    You can do that, for example WordPress.com VIP Go does this, but it will use a large amount of memory.

    Updates to core won’t revert a change like this, no.

    Before you do that, though, I would look into why this query is running. It’s not a query that runs by default on a new installation. Take a look at the stack trace and see if there’s a condition that’s firing that you can remove.

    I’ll close this off as this isn’t anything specific to Query Monitor.

    Plugin Author John Blackbourn

    (@johnbillion)

    WordPress Core Developer

    Oh wait, I’m wrong. This is a default query that comes from wp_dropdown_users() that’s used to populate the author field for bulk editing.

    Thread Starter Sina Saeedi

    (@melodymag)

    Thanks @johnbillion, I have a dedicated server with 32GB of Ram and I solved the problem by setting innodb_buffer_pool_size to 16G. It completely solved the problem and it took about 1G more of the server’s ram, not all of 16Gs. But it shouldn’t be the solution. I think it has to be solved by the WordPress guys in next updates.

    I will try the indexing solution though.

    Thread Starter Sina Saeedi

    (@melodymag)

    I was trying the index solution but I got an error and couldn’t solve it:

    Note: #1071 Specified key was too long; max key length is 3072 bytes

    I tried adding these lines in my.cnf but no luck, still got the error:

    innodb_file_format = Barracuda
    innodb_large_prefix = 1
    innodb_file_per_table = ON

    Hello, we have the same problem with a WP with 300,000 users.
    Have you found a solution to get around this problem?
    Thank you in advance

    Thread Starter Sina Saeedi

    (@melodymag)

    Hi @sympozium
    Yes, here is my solution.

    Hope it helps.

    Thank you, it works with

    innodb_buffer_pool_size=1G

    I added for the page and it works better too

    add_action( ‘init’, ‘remove_cpt_author’ );
    function remove_cpt_author() {
    remove_post_type_support(‘post’, ‘author’);
    }

    Thank you @melodymag

    Thread Starter Sina Saeedi

    (@melodymag)

    @sympozium glad to hear that 😊

    Thread Starter Sina Saeedi

    (@melodymag)

    @sympozium thanks for the solution.

    It worked great:

    add_action('init', 'remove_cpt_author');
    function remove_cpt_author() {
    	remove_post_type_support('page', 'author');
    	remove_post_type_support('post', 'author');
    }
    • This reply was modified 8 months, 3 weeks ago by Sina Saeedi.
Viewing 11 replies - 1 through 11 (of 11 total)
  • The topic ‘Slow queries in “All Posts”’ is closed to new replies.