Mod for Optimizing DB

[netfreak]

Those who are using pligg sites and having huge visitors will definitely came to know that they have already got huge db size and with a huge size with page views. Page views out are not that important I guess in accordance with the speed and smoothness of site operation.

Here is the quick solution for optimizing database by deleting page views (only out included here, cause others are needed for your users)

1. Create php file called "admin_delete_pv_out.php" and place at your root directory

<?php
include_once('Smarty.class.php');
$main_smarty = new Smarty;

include('config.php');
include(mnminclude.'html1.php');
include(mnminclude.'link.php');
include(mnminclude.'user.php');
include(mnminclude.'smartyvariables.php');
			
// require user to log in
force_authentication();

// restrict access to god only
$amIgod = 0;
$amIgod = $amIgod + checklevel('god');
$main_smarty->assign('amIgod', $amIgod);

$canIhaveAccess = 0;
$canIhaveAccess = $canIhaveAccess + checklevel('god');
$canIhaveAccess = $canIhaveAccess + checklevel('admin');

if($canIhaveAccess == 0){	
	$main_smarty->assign('tpl_center', $the_template . '/admin_templates/admin_access_denied');
	$main_smarty->display($the_template . '/pligg.tpl');		
	die();
}


function delete_pvout($pvid) {

    $query="SELECT * FROM " . table_pageviews . " WHERE pv_id = '$pvid'";
    if (! $result=mysql_query($query)) {die (mysql_error());}
    else {$sql_array = mysql_fetch_object($result); }
   	}

$sql_query = "SELECT * FROM " . table_pageviews . " WHERE pv_type = 'out'";

$result_pvout = mysql_query($sql_query);
$num_rows = mysql_num_rows($result_pvout);
		while($pvout = mysql_fetch_object($result_pvout))
		{
			delete_pvout($pvout->pvid);
		}
	
# set discards total to zero
$query="UPDATE " . table_totals . " SET total = '0' WHERE name = 'out'";
if (!mysql_query($query)) {die (mysql_error());}

	
echo $num_rows. " Page View of out links has deleted";

?>
<p><a href="admin_optimize_database.php">Click here</a> to optimize database</p>

2. Create another php file called "admin_optimize_database.php" and place at your root directory

here are the code

<?php
include_once('Smarty.class.php');
$main_smarty = new Smarty;

include('config.php');
include(mnminclude.'html1.php');
include(mnminclude.'link.php');
include(mnminclude.'user.php');
include(mnminclude.'smartyvariables.php');
			
// require user to log in
force_authentication();

// restrict access to god only
$amIgod = 0;
$amIgod = $amIgod + checklevel('god');
$main_smarty->assign('amIgod', $amIgod);

$canIhaveAccess = 0;
$canIhaveAccess = $canIhaveAccess + checklevel('god');
$canIhaveAccess = $canIhaveAccess + checklevel('admin');

if($canIhaveAccess == 0){	
	$main_smarty->assign('tpl_center', $the_template . '/admin_templates/admin_access_denied');
	$main_smarty->display($the_template . '/pligg.tpl');		
	die();
}

// $message = "";

    $query = "SHOW TABLE STATUS";
    $result=mysql_query($query);
    $table_list = "";
    while ($cur_table = mysql_fetch_object($result)) {
        $table_list .= $cur_table->Name.", ";
    }

    if (!empty($table_list)) {
        $table_list = substr($table_list, 0, -2);
        $query = "OPTIMIZE TABLE ".$table_list;
        mysql_query($query);
        $message = "Database optimized, press 'back' button to back to admin home";
    }

echo $message
?>

3. Add these few lines into your "admin_main_center.tpl" where you like to show the link.

<img src="{$my_pligg_base}/templates/{$the_template}/images/manage_backup.png" align="absmiddle"/> <a href="{$my_pligg_base}/admin_delete_pv_out.php">{#PLIGG_Visual_Header_AdminPanel_41#}</a><br/>

4. Add these few lines into your "lang_english.conf"

//<SECTION>HEADER</SECTION><ADDED>0.57</ADDED>
PLIGG_Visual_Header_AdminPanel_41 = "Optimize DB"

Tested and works fine for me

Thanks and regards

[chuckroast]

Nice work man... With your permission I'd like to move this thread to the Core Development forum...

[netfreak]

Wow ! this would be great !!!

[netwb]

i'm problem to stats to the pageviews!
i 'm install the Mod for Optimizing DB and my stats is null:
link img: you have the solution? thanks

[davemackey]

Thanks. Nice job.

[netfreak]

"netwb" Would you please make this clear to us?

Which stats you are talking about? AWstats? or ?


This mod wouldn't harm you? except the logs created for you on all outgoing which is not that much important for you. You have another pageviews like profile and story which wont be deleted if you use that mod.

Thanks

[magpie2419]

Works like a dream thanks very much I'm using Ver 9.7 so it works great with that Version.