Possible to optimize odb_get_tables() usage?
-
We were investigating a load issue on our site last week, and one thing we noticed was that running SHOW FULL PROCESSLIST showed a lot of calls ultimately traced back to this query:
SHOW FULL TABLES
FROM%s
WHERE table_type = ‘BASE TABLE’This was a red herring as far as the root cause of the load, however, it does seems like something that is unnecessary to be running on every page load, when we try to use our cache as much as possible to reduce DB queries. This plugin doesn’t seem to serve any real purpose on a normal page request, merely to optimize the database either manually or via cron, as I understand it.
Therefore, would it be possible to restructure the init logic a bit so that no db queries are ran unless the plugin is actually doing something else, such as executing its cron job, or loading the settings page, or being manually executed? I may have missed something that would make the creation of the log table necessary on a normal page load, but the core functionality we are using this plugin for would not seem to need any queries running on every single page load.
-
I am curious if the log table is ever needed before the optimizer actually starts. If so, something like this might work? Also used the page check in the init to attempt to make it before actually kicking off the optimizer, but I’m not sure that’s necessary. This moves the creation into its own function that can be called from either place. I tested deactivation, activation, ensure that when the time comes around, the odb_start function is actually triggered, the cron event comes and goes when it should, etc. Perhaps this provides some ideas at least? Also cleaned up a few seemingly unused variable references after the rearranging.
diff --git a/rvg-optimize-database.php b/rvg-optimize-database.php index f81ff37..2a5f162 100644 --- a/rvg-optimize-database.php +++ b/rvg-optimize-database.php @@ -1,7 +1,7 @@ <?php /** * @package Optimize Database after Deleting Revisions - * @version 5.0.7 + * @version 5.0.8^M */ /* Plugin Name: Optimize Database after Deleting Revisions @@ -10,7 +10,7 @@ Description: Optimizes the WordPress Database after Cleaning it out Author: CAGE Web Design | Rolf van Gelder, Eindhoven, The Netherlands Author URI: https://cagewebdev.com Network: True -Version: 5.0.7 +Version: 5.0.8^M */ /******************************************************************************************** @@ -28,8 +28,8 @@ $odb_class = new OptimizeDatabase(); class OptimizeDatabase { // VERSION - var $odb_version = '5.0.7'; - var $odb_release_date = '07/24/2021'; + var $odb_version = '5.0.8';^M + var $odb_release_date = '07/29/2021';^M // PLUGIN OPTIONS var $odb_rvg_options = array(); @@ -92,50 +92,15 @@ class OptimizeDatabase { * INITIALIZE PLUGIN *******************************************************************************/ function odb_init() { - global $wpdb; - // LOAD CLASSES $this->odb_classes(); // URLS AND DIRECTORIES $this->odb_urls_dirs(); - $this->odb_tables = $this->odb_utilities_obj->odb_get_tables(); - - // CREATE LOG TABLE (IF NOT EXISTS) - v4.6 - $this->odb_logtable_name = $wpdb->base_prefix . 'odb_logs'; - - $found = false; - for($i = 0; $i < count($this->odb_tables); $i++) { - if ($this->odb_tables[$i][0] == $this->odb_logtable_name) { - $found = true; - } - } // for($i = 0; $i < count($this->odb_tables); $i++) - - // v5.0.3 - if (!$found) { - $sql = ' - CREATE TABLE IF NOT EXISTS <code>' . $this->odb_logtable_name . '</code> ( - <code>odb_id</code> int(11) NOT NULL AUTO_INCREMENT, - <code>odb_timestamp</code> varchar(20) NOT NULL, - <code>odb_revisions</code> int(11) NOT NULL, - <code>odb_trash</code> int(11) NOT NULL, - <code>odb_spam</code> int(11) NOT NULL, - <code>odb_tags</code> int(11) NOT NULL, - <code>odb_transients</code> int(11) NOT NULL, - <code>odb_pingbacks</code> int(11) NOT NULL, - <code>odb_oembeds</code> int(11) NOT NULL, - <code>odb_orphans</code> int(11) NOT NULL, - <code>odb_tables</code> int(11) NOT NULL, - <code>odb_before</code> varchar(20) NOT NULL, - <code>odb_after</code> varchar(20) NOT NULL, - <code>odb_savings</code> varchar(20) NOT NULL, - PRIMARY KEY (<code>odb_id</code>) - ) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; - '; - - // CREATE TABLE - $wpdb->get_results($sql); + // 5.0.8 This may not even be necessary anymore^M + if ($this->odb_is_relevant_page()) {^M + $this->odb_create_log_table();^M } // GET (MULTI-SITE) NETWORK INFORMATION @@ -154,8 +119,6 @@ class OptimizeDatabase { $this->odb_minify = (defined('WP_DEBUG') && WP_DEBUG) ? '' : '.min'; // LOAD STYLE SHEET (ONLY ON RELEVANT PAGES) - $this_page = ''; - if(isset($_GET['page'])) $this_page = $_GET['page']; // v4.0.3 if($this->odb_is_relevant_page()) { wp_register_style('odb-style'.$this->odb_version, plugins_url('css/style'.$this->odb_minify.'.css', __FILE__)); @@ -575,7 +538,7 @@ class OptimizeDatabase { function odb_is_relevant_page() { $this_page = ''; if(isset($_GET['page'])) $this_page = $_GET['page']; - return ($this_page == 'odb_settings_page' || $this_page == 'rvg-optimize-database'); + return (in_array($this_page, array('odb_settings_page', 'rvg-optimize-database')));^M } // odb_is_relevant_page() @@ -644,14 +607,58 @@ class OptimizeDatabase { } // odb_start_scheduler() + function odb_create_log_table() {^M + global $wpdb;^M +^M + // PLUGIN RUNNING (v5.0.5)^M + $this->odb_tables = $this->odb_utilities_obj->odb_get_tables();^M +^M + // CREATE LOG TABLE (IF NOT EXISTS) - v4.6^M + $this->odb_logtable_name = $wpdb->base_prefix . 'odb_logs';^M +^M + $found = false;^M + for($i = 0; $i < count($this->odb_tables); $i++) {^M + if ($this->odb_tables[$i][0] == $this->odb_logtable_name) {^M + $found = true;^M + }^M + } // for($i = 0; $i < count($this->odb_tables); $i++)^M +^M + // v5.0.3^M + if (!$found) {^M + $sql = '^M + CREATE TABLE IF NOT EXISTS <code>' . $this->odb_logtable_name . '</code> (^M + <code>odb_id</code> int(11) NOT NULL AUTO_INCREMENT,^M + <code>odb_timestamp</code> varchar(20) NOT NULL,^M + <code>odb_revisions</code> int(11) NOT NULL,^M + <code>odb_trash</code> int(11) NOT NULL,^M + <code>odb_spam</code> int(11) NOT NULL,^M + <code>odb_tags</code> int(11) NOT NULL,^M + <code>odb_transients</code> int(11) NOT NULL,^M + <code>odb_pingbacks</code> int(11) NOT NULL,^M + <code>odb_oembeds</code> int(11) NOT NULL,^M + <code>odb_orphans</code> int(11) NOT NULL,^M + <code>odb_tables</code> int(11) NOT NULL,^M + <code>odb_before</code> varchar(20) NOT NULL,^M + <code>odb_after</code> varchar(20) NOT NULL,^M + <code>odb_savings</code> varchar(20) NOT NULL,^M + PRIMARY KEY (<code>odb_id</code>)^M + ) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;^M + ';^M +^M + // CREATE TABLE^M + $wpdb->query($sql);^M + }^M + }^M +^M +^M /******************************************************************************* * * MAIN METHOD FOR CLEANING / OPTIMIZING * *******************************************************************************/ function odb_start($scheduler) { - global $wpdb; - + $this->odb_create_log_table();^M +^M // PAGE LOAD TIMER $time = microtime(); $time = explode(' ', $time);
- The topic ‘Possible to optimize odb_get_tables() usage?’ is closed to new replies.
