Author Archive Pages have Slow/Duplicate DB Queries looking for Gravatars

[Ipstenu]

• [x] I've read and understood the contribution guidelines.
• [x] I've searched for any related issues and avoided creating a duplicate issue.

Please give us a description of what happened

When using YoastSEO there are double queries where it's looking for gravatar images via find_one_by_url

SELECT target_post_id
FROM wp_yoast_seo_links
WHERE url = 'https://secure.gravatar.com/avatar/[redacted]?s=500&d=mm&r=pg'
LIMIT 1

In some cases, this can cause nearly a second of slowness, depending on the size of databases. For a very large DB with a lot of images, it was half a second for each query which, ultimately, found nothing.

I was able to reproduce this on two different hosting platforms.

To Reproduce

Step-by-step reproduction instructions

1. Install Yoast
2. Visit author page
3. See dupe calls for Gravatar

Expected results

1. When the URL is gravatar, it should bail early since there's no reason to search the DB for it as an attachment.

Actual results

1. Duplicate calls looking for gravatar via find_one_by_url

Screenshots, screen recording, code snippet

Technical info

• If relevant, which editor is affected (or editors):
• [ ] Block Editor
• [ ] Gutenberg Editor
• [ ] Elementor Editor
• [ ] Classic Editor
• [ ] Other:

• Which browser is affected (or browsers):
• [x] Chrome
• [x] Firefox
• [x] Safari
• [ ] Other:

Used versions

• Device you are using: Macbook Air
• Operating system: Sonoma 14.2.1 (23C71)
• PHP version: 8.1.27 FPM CGI
• WordPress version: 6.3.3 and 6.4.5
• WordPress Theme: Bespoke custom theme
• Yoast SEO version: 22.0
• Gutenberg plugin version: N/A
• Elementor plugin version: N/A
• Classic Editor plugin version: N/A
• Relevant plugins in case of a bug:

CoAuthors Plus (latest release)

[josevarghese]

Hi @Ipstenu

Thanks for using the Yoast SEO plugin and for creating this issue.

When viewing the author page, I can replicate the duplicate calls you mentioned for the Yoast\WP\SEO\Repositories\SEO_Links_Repository->find_one_by_url(). So, I have escalated this to our development team to take a closer look at this. We will be sure to keep you posted with the updates.

[thijsoo]

Maybe start in src/helpers/open-graph/image-helper.php

[Ipstenu]

@thijsoo Would that also cover why it's searching for gravatar URLs? That was the part that confounded me. I could understand a double search if the avatar was called for multiple sizes, but the whole calling a remote URL to search locally didn't make any logical sense to me.

[Ipstenu]

Update: I tested with local avatars ( https://wordpress.org/plugins/simple-local-avatars/ ) and I get the same two duplicate queries:

SELECT `target_post_id`
FROM `wp_yoast_seo_links`
WHERE `url` = 'http://example.com/wp-content/uploads/2024/02/cropped-drawing.jpeg?quality=65&strip=all'
LIMIT 1

They're both slow as well.