Création d’un plugin

Je me lance après des semaines de lutte avec l’optimisation de la base wordpress.

Avec les dernières moutures, WordPress permet l’intégration sans code <Embed> de nombreuses sources uniquement en collant l’url.

Ce magnifique phénomène _Oembed génère donc des lignes associées de conversion du code directement dans la base de donnée, ce qui la fait gonfler d’autant. Oui, ce n’est pas exactement le process, mais les puristes me pardonneront cette approximation.

De fait, mise en place d’un petit plugin qui va :

supprimer les options Transient
supprimer les Oembed
supprimer les spams
supprimer les Locks sur les billets
supprimer les révisions (que je n’utilise que très rarement)

UPDATE :

revue du code pour lancer les opérations une par une en les encapsulant dans des fonctions.

Version avec Fonctions

<?php
/**
* Plugin Name: WP_DBCLEANER
* Plugin URI: http://pingouin-grincheux.net
* Description: removes oembed,revisions, transient options from DB 
* Version: 0.2 or whatever version of the plugin (pretty self explanatory)
* Author: Pingouin Grincheux
* Author URI: Ahttp://pingouin-grincheux.net
* License: GPL
*/


// Let's do an hourly check

// Let's do an hourly check
add_action('wp', 'activateMe');
 
function activateMe() {
	if ( !wp_next_scheduled( 'hourly_check' ) ) {
		wp_schedule_event( current_time( 'timestamp' ), 'hourly', 'hourly_check');
	}
}



add_action('hourly_check', 'remove_oembed');
 
function remove_oembed() {
	// do something every hour
    global $wpdb;
        $sql = "DELETE FROM wp_postmeta WHERE meta_key LIKE '_oembed_%'; ";
    
//get_results( $sql );
// $results = $wpdb->query($sql);  
//$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
$results = $wpdb->get_results($sql);  
}



add_action('hourly_check', 'removetransient');
 
function removetransient() {
	// do something every hour
    global $wpdb;
        $sql = "DELETE FROM wp_options WHERE option_name LIKE '%_transient_%' ; ";
    

// $results = $wpdb->query($sql);  
//$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
$results = $wpdb->get_results($sql);  
}



add_action('hourly_check', 'removespam');
 
function removespam() {
	// do something every hour
    global $wpdb;
        $sql = "DELETE FROM wp_comments WHERE comment_approved = 'spam' ; ";
    

// $results = $wpdb->query($sql);  
//$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
$results = $wpdb->get_results($sql);  
}



 
add_action('hourly_check', 'removecomments');
 
function removecomments() {
	// do something every hour
    global $wpdb;
        $sql = "DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);";
    

$results = $wpdb->query($sql);  
//$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
//$results = $wpdb->get_results($sql);  
}







add_action('hourly_check', 'removerevision');
 
function removerevision() {
	// do something every hour
    global $wpdb;
        $sql = "DELETE FROM wp_posts WHERE post_type = 'revision' ; ";
    

// $results = $wpdb->query($sql);  
//$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
$results = $wpdb->get_results($sql);  
}

add_action('hourly_check', 'removeakismet');
 
function removeakismet() {
	// do something every hour
    global $wpdb;
        $sql = "DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%'; ";


// $results = $wpdb->query($sql);  
//$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
$results = $wpdb->get_results($sql);  
}

add_action('hourly_check', 'removeeditlocks');
 
function removeeditlocks() {
	// do something every hour
    global $wpdb;
        $sql = "DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock'; ";
    

// $results = $wpdb->query($sql);  
//$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
$results = $wpdb->get_results($sql);  
}

add_action('hourly_check', 'removeeditlast');
 
function removeeditlast() {
	// do something every hour
    global $wpdb;
        $sql = "DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';";
    

// $results = $wpdb->query($sql);  
//$myrows = $wpdb->get_results( "SELECT id, name FROM mytable" );
$results = $wpdb->get_results($sql);  
}

?>

Premier Jet

<?php
/**
* Plugin Name: WP_DBCLEANER
* Plugin URI: http://pingouin-grincheux.net
* Description: removes oembed,revisions, transient options from DB 
* Version: 0.1 or whatever version of the plugin (pretty self explanatory)
* Author: Pingouin Grincheux
* Author URI: Ahttp://pingouin-grincheux.net
* License: GPL
*/


// Let's do an hourly check
define('MYPLUGIN_CLEANUP_PROBABILITY',5);

function myplugin_cleanup_db($postid) {
 global $wpdb;
 if (rand(0,99)<MYPLUGIN_CLEANUP_PROBABILITY))
 global $wpdb;
 $sql = "DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM `wp_options` WHERE `option_name` LIKE '%_transient_%' ;
DELETE FROM wp_posts WHERE post_type = 'revision' ;
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';
DELETE FROM wp_postmeta WHERE meta_key LIKE '_oembed_%'; 
DELETE FROM wp_comments WHERE comment_approved = 'spam' ;
 ";
 $results = $wpdb->query($sql)

;
}

add_action('shutdown', 'myplugin_cleanup_db');
?>

Ce code est en cours de test, donc à utiliser avec pincettes !