Slow SQL Fix

  • Richard
    Richard
    Newbie 4 points
    August 5, 2015 at 9:03 pm

    Hey Guys,

    I have around 12,000 listings in my directory and it was taking around 30 seconds to load the search results page or any category or taxonomy.

    The custom field list function was the culprit and my database just couldn’t handle the load.

    I modified class_core. php and replaced:

    $last_posts = (array)$wpdb->get_results($SQL);

    with

    If ( false === ( $last_posts = get_transient( ‘wlt_customfieldlist’))) {
    $last_posts = (array)$wpdb->get_results($SQL);
    set_transient( ‘wlt_customfieldlist’, $last_posts, 12 * HOUR_IN_SECONDS );
    }

    This drastically reduced the load time down to less than a second.

    This does require you to change the core file and it would be lost on update, Also, if you are actively adding custom fields this would likely not be a good solution.

    Please feel free to let me know if this might cause other problems and use it at your own risk.

  • Binesh
    Binesh
    Elite Member 431 points
    August 5, 2015 at 10:03 pm

    Thanks for sharing Richard. Are you on shared hosting?

  • Richard
    Richard
    Newbie 4 points
    August 5, 2015 at 11:47 pm

    It’s WPEngine which normally takes just about anything I throw at it, but on search result pages that state was throwing around 30 queries, which were taking .6 seconds a piece.

  • JD
    JD
    Newbie 47 points
    August 17, 2015 at 4:56 pm

    Richard, thanks for this excellent fix!! Have you done anything else to optimize the speed?

  • Clive
    Clive
    Super Guru 1,818 points
    August 17, 2015 at 10:24 pm

    This may have been fixed in 8.4

  • Richard
    Richard
    Newbie 4 points
    August 18, 2015 at 12:23 am

    Yes this fix has been included in the 8.4 version. JD – No I’m at 13,000 entries and the theme flies now. I have been cleaning out my database constantly and limiting the number of custom fields and taxonomies.

  • JD
    JD
    Newbie 47 points
    October 14, 2015 at 1:00 am

    Richard thanks for your answer. Can I ask you what is your code exactly doing?

    If ( false === ( $last_posts = get_transient( ‘wlt_customfieldlist’))) {
    $last_posts = (array)$wpdb->get_results($SQL);
    set_transient( ‘wlt_customfieldlist’, $last_posts, 12 * HOUR_IN_SECONDS );
    }

  • Peter
    Peter
    Newbie 22 points
    January 3, 2016 at 11:20 am

    Hi Richard,
    Thank you very much!

Viewing 8 posts - 1 through 8 (of 8 total)

New Business Themes for WordPress 2020

Start your new website today!

View Business Themes