Slow SQL Fix

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.

Created: August 5, 2015 at 9:03 pm
  • In: Directory Theme
  • Started by: RichardRichard
  • 5 members left 7 comments
  • Last reply from: PeterPeter

  • Binesh
    August 5, 2015 at 10:03 pm

    Thanks for sharing Richard. Are you on shared hosting?

  • Richard
    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
    August 17, 2015 at 4:56 pm

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

  • Clive
    August 17, 2015 at 10:24 pm

    This may have been fixed in

  • Richard
    August 18, 2015 at 12:23 am

    Yes this fix has been included in the 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
    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
    January 3, 2016 at 11:20 am

    Hi Richard,
    Thank you very much!

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