Unnecessary database queries?

Hi @almendron

I hope you are doing well.

I checked the reported query, it is a count query which happens to bring up the status in the Broken link checker menu item, could you please let us know how much time Query Monitor returns for you in the time tab?

https://monosnap.com/file/mV0aC1W2qlklDReLrN0OAc3PuRS4iT

I do believe it can improved though, I shared this with our developers so we can re-visit that behaviour.

Best Regards
Patrick Freitas

Screenshot: https://www.almendron.com/compartir/broken/query.png (note: the execution time varies).

The main problem is not the time, but why it is executed in the scope of the administration.

Example: Why is this query executed when I access the list of posts or categories?

This database query on very large sites with many posts and links causes a large RAM consumption.

https://www.almendron.com/tribuna/
xx_blc_instances: 358000 files
xx_blc_links: 289000 files
xx_blc_sych: 218000 files
xx_post: 145000 files

Hi @almendron

I pinged our BLC Team to review this closer. We will post an update here as soon as more information is available.

Kind Regards,
Kris

Another example. Sequence:

1. Login (…wp-login.php)
2. ../wp-admin: 4.3 seconds plugin query
3. ../wp-admin/plugins.php: 2.6353 seconds query plugin

Hi @almendron

Thank you for all the details. I want to inform you that our BLC has now an improvement task in that matter and future updates should fix this.

Kind Regards,
Kris

Thank you very much. I hope that the update will arrive as soon as possible.

Hi @almendron,

As explained before, this will be fixed in future updates.

I’ll go and mark this thread as resolved. Note that you can still reply on this topic.

If you have any additional questions or require further help, please let us know!

Best regards,
Laura

You have updated the plugin, but the issue raised in this query has not been resolved.

In older versions the menu showed a broken link count bubble.

The query that slows down your site is used to calculate this count. However the the count is never ever shown.

The related option 'show_link_count_bubble' can not be changed from the settings menu. But you could change it in legacy/init.php (around line 103).

The solution you propose does not work.

1. The value of ‘show_link_count_bubble’ changed to ‘false’.
2. I access the section ‘Media’, ‘Plugins’, etc. and the following query is executed in each of them:

Plugin: broken-link-checker

blcLinkQuery->get_links()
wp-content/plugins/broken-link-checker/legacy/includes/link-query.php:580

blcLinkQuery->get_filter_links()
wp-content/plugins/broken-link-checker/legacy/includes/link-query.php:682

wsBrokenLinkChecker->admin_menu()
wp-content/plugins/broken-link-checker/legacy/core/core.php:414

do_action('admin_menu')
wp-includes/plugin.php:517

SELECT COUNT(*)
FROM (
SELECT 0
FROM wp_blc_links AS links JOIN wp_blc_instances AS instances
ON links.link_id = instances.link_id
WHERE ( instances.parser_type IN ('link', 'image', 'youtube-iframe', 'metadata', 'url_field', 'acf') )
AND ( instances.container_type IN ('comment', 'post', 'page', 'dummy') )
AND ( ( broken = 1 ) )
AND ( (dismissed = 0) )
GROUP BY links.link_id) AS foo

This query should only be executed when accessing your plugin section and not in any other WordPress admin section.

I think the settings is saved as well, didn’t think of that. I think the bubble is nice. Doesn’t impact my site too much since I don’t have a zillion links. Still, I found that adding a proper index to the table helped:

ALTER TABLE {prefix}_blc_instances ADD INDEX lpc (link_id, parser_type, container_type);

To disable the bubble change line (location depend on version, but it should be the line 414 from your log).

$broken_links = 0; #$blc_link_query->get_filter_links( 'broken', array( 'count_only' => true ) );

Or remove the if block around that line completely.

(actually I think the line if (WPMUDEV_BLC\Core\Utils\Utilities::is_subsite() ) {could / should be moved to the top of that function. But I didn’t untangle the Cloud / Legacy / Local parts complety)

Hi @almendron

You have updated the plugin, but the issue raised in this query has not been resolved.

No, you’re right – this wasn’t changed yet. Usually this isn’t causing a lot of slow-downs and we didn’t have many reports on this. I can see and confirm that it’s still on the task list so it is in its queue to be changed but wasn’t included in recent release yet as there were some more “critical” issues to be addressed.

As for the solution/workaround for now:

Unless you completely wipe out all the settings of the plugin from DB first, it will not work.

A workaround that works without the need to wipe out plugin settings is to edit it directly in the DB. I wouldn’t suggest that under any circumstances otherwise, but since this is already a long time here is what you’d need to do:

– access the DB of the site (make sure to take its full backup first, just to stay on the safe side!)
– in wp_options table (note that your site may use different table prefix than “wp_”) find the option_name “wsblc_options”
– edit its option_value and you’ll see this there

"show_link_count_bubble":true,

so change it to

"show_link_count_bubble":false,

– and save edited option.

It’s not a serialized array so no need to “re-count” anything, it’s a straightforward edit.

Once saved you may need to purge server-side/object cache (if there is such cache in use) but the dreaded query will be gone.

Kind regards,
Adam

The tests were carried out on a local server. When I read your last message, I had already made the changes to the plugin. As the result was positive, I will not modify the table ‘wp_options’.

Actions performed:

Add index to the table.
ALTER TABLE {prefix}_blc_instances ADD INDEX lpc (link_id, parser_type, container_type);

Apply the suggested changes to the code

$broken_links = 0; #$blc_link_query->get_filter_links( 'broken', array( 'count_only' => true ) );

I take note of the last option proposed (modify “wsblc_options”). I will give it a try.
Thank you very much for your support.

Check. Modifying the ‘wp_options’ table also works.