Poorly Performing Code in Analytics/Data Collector

(@1benk1)

Hello,
The wp-content/plugins/embedpress/EmbedPress/Includes/Classes/Analytics/Data_Collector.php contains this code snippet:

// Shortcode: posts containing [embedpress*] shortcodes
$shortcode_count = (int) $wpdb->get_var(
    "SELECT COUNT(*) FROM {$wpdb->posts} p
     WHERE {$status_condition} {$post_type_condition}
       AND (
            p.post_content LIKE '%[embedpress %' OR
            p.post_content LIKE '%[embedpress]%' OR
            p.post_content LIKE '%[embedpress_pdf %' OR
            p.post_content LIKE '%[embedpress_document %' OR
            p.post_content LIKE '%[embedpress_calendar %'
       )"
);

…which generates the following SQL query:

SELECT COUNT(*)
FROM wp_posts p
WHERE p.post_status IN ('publish','draft','private','future')
AND p.post_type IN ('post','page','product','event','portfolio')
AND (
  p.post_content LIKE '%[embedpress %' OR
  p.post_content LIKE '%[embedpress]%' OR
  p.post_content LIKE '%[embedpress_pdf %' OR
  p.post_content LIKE '%[embedpress_document %' OR
  p.post_content LIKE '%[embedpress_calendar %'
)

Because of the five LIKE '%…%‘ conditions on post_content, the database cannot make effective use of a normal index; as a result, the query essentially scans through very large portions of wp_posts.

This SQL query takes about 1 minute to run in our WordPress installation (with > 100k posts), which is far too long and needs to be fixed urgently.

Best regards,
Ben

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

Viewing 3 replies - 1 through 3 (of 3 total)

Plugin Support Amit Paul
(@paulamit)

10 hours, 37 minutes ago

Hi @1benk1,

Greetings! Sorry to hear about the issue.

Thanks for sharing all the details with us. Regarding this, we have recently prepared a fix build, you can give it a try by downloading it from the link below.

Updated: https://d.pr/f/smD1U9

Kindly let me know how it works!

Thank you!
Thread Starter 1benk1
(@1benk1)

10 hours, 2 minutes ago
Wow, that was fast—thanks for that!

I see there are a lot of changes… it distinguishes between large and small websites, and in the case of a large site, the scan via scan_content_range is limited to only a portion of the wp_posts table.

That definitely helps and resolves this performance issue.

It would be great if the entire Analytics section, including the Data Collector, could be disabled if you don’t need that functionality.

We don’t need it and have simply bypassed it with a few lines of code:
```
add_filter( 'pre_transient_embedpress_total_content_count', function () {
	return array(
		'elementor' => 0,
		'gutenberg' => 0,
		'shortcode' => 0,
		'total'     => 0,
	);
} );
```
However, a clean hook—or even better, an option in the settings—would be more elegant.
Plugin Support Amit Paul
(@paulamit)

9 hours, 15 minutes ago

Hi @1benk1

Glad to hear the fix helps! 😊

You make a fair point. If a site doesn’t use Analytics, there should be a clean way to turn it off completely, Data Collector included, instead of working around it with a transient filter. I’ve passed your suggestion to our team so we can add a dedicated option for it.

Thanks for the kind words and the helpful suggestion. Feedback like this really does help us make EmbedPress better. 🙌

Before we wrap up: if you’re enjoying EmbedPress and our support, would you mind leaving a quick review? It helps us grow and keep improving the plugin.🙏

Link to review:
https://wordpress.org/support/plugin/embedpress/reviews/#new-post

Thanks again, and have a great day! <3

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.