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

Slow queries in “All Posts”

  • Resolved Sina Saeedi

    (@melodymag)


    2 months, 2 weeks ago

    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 6 replies - 1 through 6 (of 6 total)
  • Plugin Author John Blackbourn

    (@johnbillion)

    WordPress Core Developer

    2 months, 2 weeks ago

    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?

    Sina Saeedi

    (@melodymag)

    2 months, 2 weeks ago

    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

    2 months, 1 week ago

    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

    2 months, 1 week ago

    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.

    Sina Saeedi

    (@melodymag)

    2 months, 1 week ago

    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.

    Sina Saeedi

    (@melodymag)

    2 months, 1 week ago

    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
Viewing 6 replies - 1 through 6 (of 6 total)
  • You must be logged in to reply to this topic.

Views