File: /home/cafsindia/www/wp-content/plugins/swift-performance-lite/modules/db-optimizer/db-optimizer.php
<?php
class Swift_Performance_DB_Optimizer {
/**
* Create instance
*/
public function __construct(){
add_action('wp_ajax_swift_performance_db_optimizer', array(__CLASS__, 'ajax_handler'));
// Create schedule hooks
$options = array('optimize_tables','reindex_tables', 'clear_expired_transients', 'clear_revisions', 'clear_trashed_posts', 'clear_orphan_postmeta', 'clear_orphan_attachments', 'clear_duplicated_postmeta', 'clear_spam_comments', 'clear_trashed_comments', 'clear_orphan_commentmeta', 'clear_duplicated_commentmeta', 'clear_orphan_termmeta', 'clear_orphan_usermeta', 'clear_duplicated_usermeta');
foreach ($options as $option){
add_action('swift_performance_dbo_' . $option, array(__CLASS__, $option));
}
// Create cron schedules
add_filter( 'cron_schedules', function ($schedules){
// Common cache
$schedules['swift_performance_dbo_weekly'] = array(
'interval' => 604800,
'display' => 'Weekly'
);
$schedules['swift_performance_dbo_twice_monthly'] = array(
'interval' => 1209600,
'display' => 'Twice_monthly'
);
$schedules['swift_performance_dbo_monthly'] = array(
'interval' => 2592000,
'display' => 'Monthly'
);
return $schedules;
});
}
/**
* Print the schedule/unshedule action link
*/
public static function schedule($option){
$class = (wp_next_scheduled('swift_performance_dbo_' . $option) ? ' is-scheduled' : '');
echo '<a href="#" id="trigger-'.esc_attr($option).'" class="swift-toggle-scheduled-dbo'.$class.'" data-option="'.esc_attr($option).'"><strong><i class="far fa-clock"></i></strong></a>';
}
/**
* Print schedule form
*/
public static function schedule_form($option){
$freq = wp_get_schedule('swift_performance_dbo_' . $option);
echo '<form id="schedule-'.esc_attr($option).'" class="swift-dbo-schedule-selector swift-hidden">';
echo '<input id="swift_performance_dbo_'.esc_attr($option).'_twicedaily" ' . checked($freq, 'twicedaily', false) . ' name="freq" type="radio" value="twicedaily"><label for="swift_performance_dbo_'.esc_attr($option).'_twicedaily">'.esc_html__('12 hours', 'swift-performance').'</label>';
echo '<input id="swift_performance_dbo_'.esc_attr($option).'_daily" ' . checked($freq, 'daily', false) . ' name="freq" type="radio" value="daily"><label for="swift_performance_dbo_'.esc_attr($option).'_daily">'.esc_html__('Daily', 'swift-performance').'</label>';
echo '<input id="swift_performance_dbo_'.esc_attr($option).'_weekly" ' . checked($freq, 'swift_performance_dbo_weekly', false) . ' name="freq" type="radio" value="swift_performance_dbo_weekly"><label for="swift_performance_dbo_'.esc_attr($option).'_weekly">'.esc_html__('Weekly', 'swift-performance').'</label>';
echo '<input id="swift_performance_dbo_'.esc_attr($option).'_twice_monthly" ' . checked($freq, 'swift_performance_dbo_twice_monthly', false) . ' name="freq" type="radio" value="swift_performance_dbo_twice_monthly"><label for="swift_performance_dbo_'.esc_attr($option).'_twice_monthly">'.esc_html__('Biweekly', 'swift-performance').'</label>';
echo '<input id="swift_performance_dbo_'.esc_attr($option).'_monthly" ' . checked($freq, 'swift_performance_dbo_monthly', false) . ' name="freq" type="radio" value="swift_performance_dbo_monthly"><label for="swift_performance_dbo_'.esc_attr($option).'_monthly">'.esc_html__('Monthly', 'swift-performance').'</label>';
echo '<div class="swift-button-container"><a href="#" class="swift-scheduled-dbo-change swift-btn swift-btn-green swift-btn-small" data-action="set-schedule">'.esc_html__('Save', 'swift-performance').'</a><a href="#" class="swift-scheduled-dbo-change swift-btn swift-btn-black swift-btn-small" data-action="clear-schedule">'.esc_html__('Clear', 'swift-performance').'</a></div>';
echo '<input type="hidden" name="option" value="'.esc_attr($option).'"></form>';
}
/**
* Global AJAX handler
*/
public static function ajax_handler(){
if (!current_user_can('manage_options') || !isset($_REQUEST['_wpnonce']) || !wp_verify_nonce($_REQUEST['_wpnonce'], 'swift-performance-ajax-nonce')){
echo __('Your session has expired. Please refresh the page and try again.', 'swift-performance');
die;
}
// Extend timeout
Swift_Performance::set_time_limit(120, 'db_optimizer_ajax');
$action = isset($_REQUEST['swift-action']) ? $_REQUEST['swift-action'] : '';
switch ($action) {
case 'optimize-tables':
Swift_Performance_DB_Optimizer::optimize_tables();
echo sprintf(__('%d tables optimized', 'swift-performance'), Swift_Performance_DB_Optimizer::count_tables());
break;
case 'reindex-tables':
Swift_Performance_DB_Optimizer::reindex_tables();
echo sprintf(__('%d tables reindexed', 'swift-performance'), Swift_Performance_DB_Optimizer::count_tables());
break;
case 'clear-expired-transients':
Swift_Performance_DB_Optimizer::clear_expired_transients();
echo Swift_Performance_DB_Optimizer::count_expired_transients();
break;
case 'clear-revisions':
Swift_Performance_DB_Optimizer::clear_revisions();
echo Swift_Performance_DB_Optimizer::count_revisions();
break;
case 'clear-trashed-posts':
Swift_Performance_DB_Optimizer::clear_trashed_posts();
echo Swift_Performance_DB_Optimizer::count_trashed_posts();
break;
case 'clear-orphan-postmeta':
Swift_Performance_DB_Optimizer::clear_orphan_postmeta();
echo Swift_Performance_DB_Optimizer::count_orphan_postmeta();
break;
case 'clear-orphan-attachments':
Swift_Performance_DB_Optimizer::clear_orphan_attachments();
echo Swift_Performance_DB_Optimizer::count_orphan_attachments();
break;
case 'clear-duplicated-postmeta':
Swift_Performance_DB_Optimizer::clear_duplicated_postmeta();
echo Swift_Performance_DB_Optimizer::count_duplicated_postmeta();
break;
case 'clear-spam-comments':
Swift_Performance_DB_Optimizer::clear_spam_comments();
echo Swift_Performance_DB_Optimizer::count_spam_comments();
break;
case 'clear-trashed-comments':
Swift_Performance_DB_Optimizer::clear_trashed_comments();
echo Swift_Performance_DB_Optimizer::count_trashed_comments();
break;
case 'clear-orphan-commentmeta':
Swift_Performance_DB_Optimizer::clear_orphan_commentmeta();
echo Swift_Performance_DB_Optimizer::count_orphan_commentmeta();
break;
case 'clear-duplicated-commentmeta':
Swift_Performance_DB_Optimizer::clear_duplicated_commentmeta();
echo Swift_Performance_DB_Optimizer::count_duplicated_commentmeta();
break;
case 'clear-orphan-termmeta':
Swift_Performance_DB_Optimizer::clear_orphan_termmeta();
echo Swift_Performance_DB_Optimizer::count_orphan_termmeta();
break;
case 'clear-orphan-usermeta':
Swift_Performance_DB_Optimizer::clear_orphan_usermeta();
echo Swift_Performance_DB_Optimizer::count_orphan_usermeta();
break;
case 'clear-duplicated-usermeta':
Swift_Performance_DB_Optimizer::clear_duplicated_usermeta();
echo Swift_Performance_DB_Optimizer::count_duplicated_usermeta();
break;
case 'set-schedule':
$hook = 'swift_performance_dbo_' . $_REQUEST['option'];
wp_clear_scheduled_hook($hook);
wp_schedule_event(time(), $_REQUEST['freq'], $hook);
Swift_Performance_DB_Optimizer::schedule($_REQUEST['option']);
break;
case 'clear-schedule':
$hook = 'swift_performance_dbo_' . $_REQUEST['option'];
wp_clear_scheduled_hook($hook);
Swift_Performance_DB_Optimizer::schedule($_REQUEST['option']);
break;
}
die;
}
/**
* Count expired transients
* @return int
*/
public static function count_expired_transients(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE '\_transient\_timeout\__%%' AND option_value < NOW()");
}
/**
* Count revisions
* @return int
*/
public static function count_revisions(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE `post_type` LIKE 'revision'");
}
/**
* Count trashed posts
* @return int
*/
public static function count_trashed_posts(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE `post_status` LIKE 'trash'");
}
/**
* Get autoload size
* @return int
*/
public static function get_autoload_size(){
global $wpdb;
return $wpdb->get_var("SELECT SUM(LENGTH(option_value)) as autoload_size FROM {$wpdb->options} WHERE autoload='yes'");
}
/**
* Count orphan postmeta
* @return int
*/
public static function count_orphan_postmeta(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE post_id NOT IN (SELECT ID FROM {$wpdb->posts})");
}
/**
* Count orphan attachments
* @return int
*/
public static function count_orphan_attachments(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_parent NOT IN (SELECT ID FROM {$wpdb->posts}) AND post_parent != 0 AND post_type = 'attachment'");
}
/**
* Count duplicated post meta
* @return int
*/
public static function count_duplicated_postmeta(){
global $wpdb;
return count($wpdb->get_results("SELECT pm.meta_id AS meta_id, pm.post_id AS post_id FROM {$wpdb->postmeta} pm INNER JOIN (SELECT post_id, meta_key, meta_value, COUNT(*) FROM {$wpdb->postmeta} GROUP BY post_id, meta_key, meta_value HAVING COUNT(*) > 1) pm2 ON pm.post_id = pm2.post_id AND pm.meta_key = pm2.meta_key AND pm.meta_value = pm2.meta_value WHERE pm.meta_key NOT IN ('_price', '_used_by')"));
}
/**
* Count spam comments
* @return int
*/
public static function count_spam_comments(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->comments WHERE comment_approved = 'spam'");
}
/**
* Count trashed comments
* @return int
*/
public static function count_trashed_comments(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->comments WHERE comment_approved = 'trash'");
}
/**
* Count orphan commentmeta
* @return int
*/
public static function count_orphan_commentmeta(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->commentmeta} WHERE comment_id NOT IN (SELECT comment_ID FROM {$wpdb->comments})");
}
/**
* Count orphan termmeta
* @return int
*/
public static function count_orphan_termmeta(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->termmeta} WHERE term_id NOT IN (SELECT term_id FROM {$wpdb->terms})");
}
/**
* Count orphan usermeta
* @return int
*/
public static function count_orphan_usermeta(){
global $wpdb;
return $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->usermeta} WHERE user_id NOT IN (SELECT ID FROM {$wpdb->users})");
}
/**
* Count duplicated commentmeta
* @return int
*/
public static function count_duplicated_commentmeta(){
global $wpdb;
return count($wpdb->get_results("SELECT cm.meta_id AS meta_id, cm.comment_id AS comment_id FROM {$wpdb->commentmeta} cm INNER JOIN (SELECT comment_id, meta_key, meta_value, COUNT(*) FROM {$wpdb->commentmeta} GROUP BY comment_id, meta_key, meta_value HAVING COUNT(*) > 1) cm2 ON cm.comment_id = cm2.comment_id AND cm.meta_key = cm2.meta_key AND cm.meta_value = cm2.meta_value"));
}
/**
* Count duplicated usermeta
* @return int
*/
public static function count_duplicated_usermeta(){
global $wpdb;
return count($wpdb->get_results("SELECT um.umeta_id AS umeta_id, um.user_id AS user_id FROM {$wpdb->usermeta} um INNER JOIN (SELECT user_id, meta_key, meta_value, COUNT(*) FROM {$wpdb->usermeta} GROUP BY user_id, meta_key, meta_value HAVING COUNT(*) > 1) um2 ON um.user_id = um2.user_id AND um.meta_key = um2.meta_key AND um.meta_value = um2.meta_value"));
}
/**
* Count tables
* @return int
*/
public static function count_tables(){
global $wpdb;
$tables = $wpdb->get_results("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME LIKE '{$wpdb->prefix}%'");
return count($tables);
}
/**
* Get 10 largest autoloaded options
* @return array
*/
public static function get_largest_autoloaded_options(){
global $wpdb;
return $wpdb->get_results("SELECT option_name, LENGTH(option_value) AS size FROM {$wpdb->options} WHERE autoload='yes' ORDER BY size DESC LIMIT 10");
}
/**
* Clear expired transients
*/
public static function clear_expired_transients(){
global $wpdb;
$options = $wpdb->get_col("SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE '_transient_timeout_%' AND option_value < NOW()");
foreach ($options as $option) {
if ( strpos( $option, '_site_transient_' ) !== false ) {
delete_site_transient( str_replace( '_site_transient_timeout_', '', $option ) );
} else {
delete_transient( str_replace( '_transient_timeout_', '', $option ) );
}
}
}
/**
* Clear revisions
*/
public static function clear_revisions(){
global $wpdb;
$posts = $wpdb->get_col("SELECT ID FROM {$wpdb->posts} WHERE `post_type` LIKE 'revision'");
foreach ($posts as $id) {
wp_delete_post_revision($id);
}
}
/**
* Clear trashed posts
*/
public static function clear_trashed_posts(){
global $wpdb;
$posts = $wpdb->get_col("SELECT ID FROM {$wpdb->posts} WHERE `post_status` LIKE 'trash'");
foreach ($posts as $id) {
wp_delete_post($id, true);
}
}
/**
* Clear orphan postmeta
*/
public static function clear_orphan_postmeta(){
global $wpdb;
$postmeta = $wpdb->get_results("SELECT meta_id, post_id, meta_key FROM {$wpdb->postmeta} WHERE post_id NOT IN (SELECT ID FROM {$wpdb->posts})");
foreach ($postmeta as $meta) {
if (empty($meta->post_id)) {
$wpdb->query("DELETE FROM $wpdb->postmeta WHERE meta_id = {$meta->meta_id}");
} else {
delete_post_meta( $meta->post_id, $meta->meta_key );
}
}
}
/**
* Clear orphan attachments
*/
public static function clear_orphan_attachments(){
global $wpdb;
$attachments = $wpdb->get_col("SELECT ID FROM {$wpdb->posts} WHERE post_parent NOT IN (SELECT ID FROM {$wpdb->posts}) AND post_parent != 0 AND post_type = 'attachment'");
foreach ($attachments as $attachment) {
wp_delete_attachment($attachment, true);
}
}
/**
* Clear duplicated post meta
*/
public static function clear_duplicated_postmeta(){
global $wpdb;
$_postmeta = array();
$postmeta = $wpdb->get_results("SELECT pm.meta_id AS meta_id, pm.post_id AS post_id FROM {$wpdb->postmeta} pm INNER JOIN (SELECT post_id, meta_key, meta_value, COUNT(*) FROM {$wpdb->postmeta} GROUP BY post_id, meta_key, meta_value HAVING COUNT(*) > 1) pm2 ON pm.post_id = pm2.post_id AND pm.meta_key = pm2.meta_key AND pm.meta_value = pm2.meta_value WHERE pm.meta_key NOT IN ('_price', '_used_by')");
foreach($postmeta as $meta){
$_postmeta[$meta->post_id][] = $meta->meta_id;
}
foreach ($_postmeta as $post_id => $meta_ids) {
array_pop($meta_ids);
$wpdb->query("DELETE FROM $wpdb->postmeta WHERE meta_id IN (" . implode( ',', $meta_ids ) . ")");
}
}
/**
* Clear spam comments
*/
public static function clear_spam_comments(){
global $wpdb;
$comments = $wpdb->get_col("SELECT comment_ID FROM $wpdb->comments WHERE comment_approved = 'spam'");
foreach ( $comments as $comment ) {
wp_delete_comment($comment, true );
}
}
/**
* Clear trashed comments
*/
public static function clear_trashed_comments(){
global $wpdb;
$comments = $wpdb->get_col("SELECT comment_ID FROM $wpdb->comments WHERE comment_approved = 'trash'");
foreach ( $comments as $comment ) {
wp_delete_comment($comment, true );
}
}
/**
* Clear orphan commentmeta
*/
public static function clear_orphan_commentmeta(){
global $wpdb;
$commentmeta = $wpdb->get_results("SELECT meta_id, comment_id, meta_key FROM {$wpdb->commentmeta} WHERE comment_id NOT IN (SELECT comment_ID FROM {$wpdb->comments})");
foreach ($commentmeta as $meta) {
if (empty($meta->comment_id)) {
$wpdb->query("DELETE FROM $wpdb->commentmeta WHERE meta_id = {$meta->meta_id}");
} else {
delete_comment_meta( $meta->comment_id, $meta->meta_key );
}
}
}
/**
* Clear orphan termmeta
*/
public static function clear_orphan_termmeta(){
global $wpdb;
$termmeta = $wpdb->get_results("SELECT meta_id, term_id, meta_key FROM {$wpdb->termmeta} WHERE term_id NOT IN (SELECT term_id FROM {$wpdb->terms})");
foreach ($termmeta as $meta) {
if (empty($meta->term_id)) {
$wpdb->query("DELETE FROM $wpdb->termmeta WHERE meta_id = {$meta->meta_id}");
} else {
delete_term_meta( $meta->term_id, $meta->meta_key );
}
}
}
/**
* Clear orphan usermeta
*/
public static function clear_orphan_usermeta(){
global $wpdb;
$usermeta = $wpdb->get_results("SELECT umeta_id, user_id, meta_key FROM {$wpdb->usermeta} WHERE user_id NOT IN (SELECT ID FROM {$wpdb->users})");
foreach ($usermeta as $meta) {
if (empty($meta->user_id)) {
$wpdb->query("DELETE FROM $wpdb->usermeta WHERE meta_id = {$meta->umeta_id}");
} else {
delete_user_meta( $meta->user_id, $meta->meta_key );
}
}
}
/**
* Clear duplicated commentmeta
*/
public static function clear_duplicated_commentmeta(){
global $wpdb;
$_commentmeta = array();
$commentmeta = $wpdb->get_results("SELECT cm.meta_id AS meta_id, cm.comment_id AS comment_id FROM {$wpdb->commentmeta} cm INNER JOIN (SELECT comment_id, meta_key, meta_value, COUNT(*) FROM {$wpdb->commentmeta} GROUP BY comment_id, meta_key, meta_value HAVING COUNT(*) > 1) cm2 ON cm.comment_id = cm2.comment_id AND cm.meta_key = cm2.meta_key AND cm.meta_value = cm2.meta_value");
foreach($commentmeta as $meta){
$_commentmeta[$meta->comment_id][] = $meta->meta_id;
}
foreach ($_commentmeta as $comment_id => $meta_ids) {
array_pop($meta_ids);
$wpdb->query("DELETE FROM $wpdb->commentmeta WHERE meta_id IN (" . implode( ',', $meta_ids ) . ")");
}
}
/**
* Clear duplicated usermeta
*/
public static function clear_duplicated_usermeta(){
global $wpdb;
$_usermeta = array();
$usermeta = $wpdb->get_results("SELECT um.umeta_id AS umeta_id, um.user_id AS user_id FROM {$wpdb->usermeta} um INNER JOIN (SELECT user_id, meta_key, meta_value, COUNT(*) FROM {$wpdb->usermeta} GROUP BY user_id, meta_key, meta_value HAVING COUNT(*) > 1) um2 ON um.user_id = um2.user_id AND um.meta_key = um2.meta_key AND um.meta_value = um2.meta_value");
foreach($usermeta as $meta){
$_usermeta[$meta->user_id][] = $meta->umeta_id;
}
foreach ($_usermeta as $user_id => $meta_ids) {
array_pop($meta_ids);
$wpdb->query("DELETE FROM $wpdb->usermeta WHERE umeta_id IN (" . implode( ',', $meta_ids ) . ")");
}
}
/**
* Optimize all tables
*/
public static function optimize_tables(){
global $wpdb;
$tables = $wpdb->get_col("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME LIKE '{$wpdb->prefix}%'");
$tables = implode( ',', $tables );
$wpdb->query("OPTIMIZE TABLE {$tables}");
}
/**
* Reindex all tables which match the prefix
* @param string $prefix
*/
public static function reindex_tables($table = ''){
global $wpdb;
$like = $wpdb->prefix . $table . '%';
$tables = $wpdb->get_results($wpdb->prepare("SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME LIKE %s", $like));
foreach ($tables as $table){
$table_name = esc_sql($table->TABLE_NAME);
switch ($table->ENGINE){
case 'MyISAM':
$wpdb->query("REPAIR TABLE {$table_name}");
break;
case 'InnoDB':
$wpdb->query("ALTER TABLE {$table_name} ENGINE = InnoDB");
break;
}
}
}
}
return new Swift_Performance_DB_Optimizer();
?>