A core query is taking really long to execute

  • rohitmanglik

    (@rohitmanglik)


    7 years, 1 month ago

    Hi,

    Background: I have a very heavy site with 60k Posts. I am running it in 32GB Server with 2 Intel Xeon Processors and SSD Drives.

    When I try to create a new post, it takes really long to load the page. According to Query Monitor Plugin, a wordpress core query is taking more than 8seconds to execute.

    Here is the screenshot: (Everything is self explanatory): http://pasteboard.co/MDqG9riFA.png

    Can you please guide of how to optimize this. (I am not a coder).

    Query:
    `SELECT DISTINCT meta_key
    FROM wp_6_postmeta
    WHERE meta_key NOT BETWEEN ‘_’
    AND ‘_z’
    HAVING meta_key NOT LIKE ‘\\_%’
    ORDER BY meta_key
    LIMIT 30`

    Thanks

Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    7 years, 1 month ago

    have you tuned your mysql server?

    Thread Starter rohitmanglik

    (@rohitmanglik)

    7 years, 1 month ago

    Yes, I am using managed hosting.

    Is there any specific tuning I need to do? I will ask my hosting provider to do it.

    Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    7 years, 1 month ago

    I use the script mysqltuner.pl to look at the stats and look at queries and make sure everything used in a WHERE clause is indexed.

    Dion

    (@diondesigns)

    7 years, 1 month ago

    WHERE meta_key NOT BETWEEN ‘_’
    AND ‘_z’
    HAVING meta_key NOT LIKE ‘\\_%’

    Oh. My. Goodness. That portion of the query is an utter nightmare, and to be honest, given the size of your site I’m surprised the query only takes 8 seconds to execute. It’s like a rogue’s gallery of what NOT to do in a query. (Emphasis on NOT…even though it’s supported by MySQL, one should never put NOT in front of BETWEEN or LIKE.)

    The WordPress DB schema is inefficient. Its design causes sites to get slower and slower as more posts and users are added. One thing within the DB that might help you is to create a new postmeta table with meta_key having a FULLTEXT index, then copying all the data to the table, and finally doing some table renaming to make the new postmeta table active. You’ll need to disable the “db_delta” system in WordPress if you do this.

    If your core WordPress tables aren’t InnoDB, you should convert the tables to InnoDB (MySQL 5.6+ or MariaDB 10+ required if you add the FULLTEXT index). You should also have someone (perhaps a DBA) look at your MySQL configuration. mysqltuner is OK at best; all it does is make general recommendations, and due to its generality, some of its recommendations will do more harm than good. But it IS better than doing nothing at all.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘A core query is taking really long to execute’ is closed to new replies.