WordPress Database Queries Optimization

Comment on Stallion Responsive WordPress SEO Theme by SEO Dave.

WordPress core does some weird stuff with widget SQL queries that has a negative impact on performance.

Been trying to optimize Stallion Responsive to reduce the number of database calls etc… and during the database connection optimization process found WordPress core adds four database queries for some of the default widgets even when the widgets aren’t active!

If this was only in the backend (when we are logged into the WordPress Dashboard) that’s OK, doesn’t have a negative user experience, but this is frontend as well so hurts performance for no gain! Picture a website with 1,000,000 pageviews a month, in could be 4,000,0000 unnecessary database calls for no gain!

The issue is caused by WordPress Nt setting default widget options for these four widgets, which forces WordPress to connect to the database on every page load with these queries (I wonder why it has to check if no defaults are set?):

Query: SELECT option_value FROM wp_options WHERE option_name = 'widget_pages' LIMIT 1
Query: SELECT option_value FROM wp_options WHERE option_name = 'widget_calendar' LIMIT 1
Query: SELECT option_value FROM wp_options WHERE option_name = 'widget_tag_cloud' LIMIT 1
Query: SELECT option_value FROM wp_options WHERE option_name = 'widget_nav_menu' LIMIT 1

I’m not going to pretend to be an SQL expert, but I do know we shouldn’t be querying an option for widgets we aren’t using: I don’t use the Pages Widget (use custom menus), the calendar widget is awful SEO wise (daily archives, eeek) and had it blocked (unregistered the widget) from loading in Stallion Responsive 8.0 which just so happens to prevent the calendar widget database query issue for the calendar widget (I smell an unregister widget solution).

I think the issue is if you haven’t added a default WordPress widget to a sidebar the relevant database entry in the main options table doesn’t exist.

I’m familiar with unregister_widget which unregisters widgets :-) which means the widgets no longer load/exist to WordPress, sort of code you’d use to unregister widgets you aren’t using:

unregister_widget('WP_Widget_Pages');
unregister_widget('WP_Widget_Calendar');
unregister_widget('WP_Widget_Tag_Cloud');
unregister_widget('WP_Nav_Menu_Widget');

My first thought was add some Stallion options to selectively unregister the default WordPress widgets. Being a perfectionist in Stallion 8.1 you can unregister any or all of the 13 WP default widgets at the click of a mouse (13 tick boxes).

I also found the same issue with some Stallion Responsive widgets (no defaults added to the database), so also added options for all the Stallion widgets that’s can’t be disabled another way (17 more tick boxes): for example the content and links unit AdSense widgets can be disabled already on the AdSense options page, but the Google AdSense search widget couldn’t be disabled (unless AdSense was turned off completely) so a new tick box.

The new options are under “Stallion Theme” >> “Performance Options” in Stallion 8.1+.

With the right settings you can set “Appearance” >> “Widgets” to load show no default WordPress widgets or Stallion widgets. Weird seeing the widgets admin page with no widgets available.

This solves the issue IF a user selectively unregisters widgets they aren’t using. Only problem with this is that’s not going to happen, most of you are going to leave the widgets available even if you aren’t using them.

Found another solution.

WordPress doesn’t run these database queries if there’s an option within the main options table, if you’ve created a Pages widget the option exists and the database query isn’t called.

Since we don’t want to go manually checking which widgets have a database entry already the solution is to use the add_option WordPress code like this below:

add_option( 'widget_pages', array ( '_multiwidget' => 1 ) );
add_option( 'widget_calendar', array ( '_multiwidget' => 1 ) );
add_option( 'widget_tag_cloud', array ( '_multiwidget' => 1 ) );
add_option( 'widget_nav_menu', array ( '_multiwidget' => 1 ) );

Have some similar code for the Stallion widgets with the same problem.

This will add an option for the four problem widgets above if the option doesn’t already exist (we don’t want to overwrite any widget options you are using). I’ve wrapped the above code in a function that’s only called on Stallion Activation, during the Stallion Responsive 8.1 update or if you delete all theme options and reactivate Stallion. That’s just being efficient with the code, only needs adding once rather than checked every page load.

Interestingly since the four widget add_options are not deleted when Stallion is deactivated (activate another theme) or even delete all the Stallion main options. Means activating Stallion 8.1 followed by deactivating and reactivating another theme solves this issue for other WordPress themes. Those testing Stallion Responsive 8.1+ will gain a small performance boost with other themes in the future.

Phew….

So to solve a handful of database queries WordPress is loading that we don’t need and has a negative performance impact, we have 30 new options for disabling widgets one by one and code to add some database options. In hindsight could have solved this with just the add_options code, but I do like the option of selectively disabling widgets we don’t use.

For the WordPress core widgets it might not have a big impact on performance unregistering widgets because the widget code is still loaded, then ‘unloaded’ (unregistered). For Stallion I’ve not used the unregister widget code, instead I’ve set the widget code to never load. Basically if you set a Stallion widget not to load it’s the equivalent of hacking into the themes php files, finding each widget and deleting the code so it no longer exists. This will have a small performance improvement.

When Stallion Responsive 8.1 is released I strongly suggest after setting up widgets, go to “Stallion Theme” >> “Performance Options” and selectively disable any widgets you haven’t used. I’ve set some of the SEO damaging WordPress core widgets to not load by default and some of the less used Stallion widgets also not to load.

Should of titled this comment how to squeeze a few millisecond faster loading time out of WordPress by spending a day researching WordPress database queries :-)

Quite extreme what we have to resort to now to keep Google happy SEO wise.

Anyone else noticed the Google PageSpeed Insights Tool Results has the Mobile User Experience at the top of the results now? Suggests Google considers user experience more important than speed: I know they are both linked, but you can argue waiting half a second longer for a page to load is not as important as not being able to read the font or easily click a tap target.

David