Offload Media Adds 6 seconds of MySQL Queries

  • Resolved thesoundbully39

    (@thesoundbully39)


    3 years, 2 months ago

    I know the support policy. Hoping for community contribution on this one.. I don’t have a paid license yet as we aren’t sure whether we’ll go forward.

    Current production site (not running Divi) does not experience this issue.
    New site in staging does.

    The new site is Divi based, MySQL 8, NGINX, Memcached, PHP-FPM.

    With the offload plugin enabled, an additional 6-8 seconds of MySQL query time occurs. This is determined through Query Monitor.

    All slow queries are similar to:

    SELECT *
FROM wp_as3cf_items
WHERE source_type = 'media-library'
AND (path LIKE '%uploads/2020/11/04020032/Wigerts-Bonsai-logos-1.png'
OR original_path LIKE '%uploads/2020/11/04020032/Wigerts-Bonsai-logos-1.png');

    There are somewhere around 20,000 images on this S3 bucket. The S3 bucket is then served via Cloudflare.

    Again, this does not happen on the same server with the production site.

    I’m baffled.

    The page I need help with: [log in to see the link]

Viewing 5 replies - 1 through 5 (of 5 total)
  • Thread Starter thesoundbully39

    (@thesoundbully39)

    Hmm.. so I noticed that on the production site it does not use the same query.. no LIKE in the statements. Why would it be different? Divi?

    Thread Starter thesoundbully39

    (@thesoundbully39)

    So, to answer my own question (kind of). The slowness is because a different method needs to be used to find the media ID due to how Divi stores data.

    However, I have replaced the MySQL query in the get_source_id_by_remote_url() function within item.php that contains LIKE (super slow) and have shaved that 5-6 seconds off of the queries…

    Please tell me the drawback of what I’ve done!!

    /* ORIGINAL BEFORE MODIFIED BY TYLER TO REDUCE MYSQL LIKE QUERY
		 $sql = $wpdb->prepare(
			"SELECT * FROM " . static::items_table() . " WHERE source_type = %s AND (path LIKE %s OR original_path LIKE %s);"
			, static::$source_type
			, '%' . $path
			, '%' . $path
		);
MODIFIED BELOW */
		$sql = $wpdb->prepare(
			"SELECT * FROM " . static::items_table() . " WHERE source_type = %s AND path = %s;"
			, static::$source_type
			, '%' . $path
			, '%' . $path
		);
/* END MODIFICATION */
    Plugin Author Delicious Brains

    (@deliciousbrains)

    The problem is likely due to using Divi on the new site, WP Offload Media (not Lite) has built in support for Divi.

    Probably best that you try out WP Offload Media to see if that fixes the issue. We offer a 100% No-Risk 60-Day Money Back Guarantee. If for any reason you are not happy with our product or service, simply let us know within 60 days of your purchase and we’ll refund 100% of your money. No questions asked.

    As for the SQL changes … your version is problematic as scaled images will likely not have their URLs properly rewritten in various scenarios.

    -IJ

    Thread Starter thesoundbully39

    (@thesoundbully39)

    How does the paid version differ for divi support? Does it use faster queries than the existing that contain LIKE?

    Plugin Author Delicious Brains

    (@deliciousbrains)

    The paid version of WP Offload Media has integration code for Divi that helps in rewriting the URLs that Divi saves or displays. Should make things faster once you’ve edited and saved any problematic Divi pages.

    Contact us via the “Support” tab in WP Offload Media if you still have issues though and we’ll try and help you out.

    -IJ

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Offload Media Adds 6 seconds of MySQL Queries’ is closed to new replies.