#1 By: avenueb Subject:Optimizing the Datbase  Time: 04.Oct.05 04:22:13
Occassionally I will run the "optimize database" option in the supermod admin. 

I have to admit I have no idea what its really doing.. 

Your database contains 52 table(s)
Attempting to optimize your database...
optimize: yabbse_antifloodreg .... 0.984 kb optimized.
optimize: yabbse_boards .... 0.152 kb optimized.
optimize: yabbse_collapse .... 0.012 kb optimized.
optimize: yabbse_log_boards .... 1.66 kb optimized.
optimize: yabbse_log_clicks .... 226.926 kb optimized.
optimize: yabbse_log_floodcontrol .... 0.039 kb optimized.
optimize: yabbse_log_karma .... 0.07 kb optimized.
optimize: yabbse_log_mark_read .... 0.01 kb optimized.
optimize: yabbse_log_online .... 0.563 kb optimized.
optimize: yabbse_log_topics .... 9.949 kb optimized.
optimize: yabbse_magic_llamas .... 0.336 kb optimized.
optimize: yabbse_members .... 12.031 kb optimized.
optimize: yabbse_messages .... 614.285 kb optimized.
optimize: yabbse_topics .... 5.031 kb optimized.

14 table(s) optimized.


#2 By: webmaster Subject:Optimizing the Datbase  Time: 04.Oct.05 14:55:24
To be honest I dont really know what it is doing either but here is the code.. Maybe someone else will understand what it is doing..

Code:

function OptimizeTables()
{
global $db_name, $webmaster_email, $txt, $yytitle;
$message = '';
$yytitle = $txt['yse281'];
template_header();
$query = "SHOW TABLE STATUS FROM `$db_name`";
$get_tables = mysql_query($query) or database_error(__FILE__, __LINE__);
$num_tabs = mysql_num_rows($get_tables);
$output_handling = array();
$mail_message = array();
$head = $txt['yse282'] . $num_tabs . ' ' . $txt['yse282b'];
$output_handling = array($head);
if (0
< $num_tabs) {
$x = $txt['yse283'];
$output_handling = array_merge($output_handling, $x);
$k = 0;
$i = 0;
while ($table = mysql_fetch_array($get_tables)) {
$tableName = mysql_tablename($get_tables, $i);
$datalength = $table['Data_length'];
$indexlength = $table['Index_length'];
$length = $datalength + $indexlength;
$length = $length / 1024;
$length = round ($length, 3);
$freedata = $table['Data_free'];
$freedata = $freedata / 1024;
$freedata = round ($freedata, 3);
// optimize
$query = "OPTIMIZE TABLE " . $tableName;
$resultat = mysql_query($query) or database_error(__FILE__, __LINE__);
if ($freedata == 0)
$k++;
else {
$x = "optimize: $tableName .... $freedata $txt[yse284]\n";
$output_handling = array_merge($output_handling, $x);
$mail_message = array_merge($mail_message, $x);
$opttab++;
}
$i++;
}
$cnt = $num_tabs - $k;
if ($cnt == 0)
$output_handling = array_merge($output_handling, '
<br />
' . $txt['yse285']);
else {
$x = "
<br />
" . $opttab . $txt['yse286'];
$output_handling = array_merge($output_handling, $x);
}
} // end if
echo "
<br />
<br />
";
$ret = array_values($output_handling);
foreach ($ret as $var)
echo "$var
<br />
";
$ret = array_values($mail_message);
$array_cnt = count($mail_message);
foreach ($ret as $var)
$message .= $var;
if ($array_cnt == 0)
$message = $txt['yse285b'];
// mail($webmaster_email, $txt['yse281'], $message, "From: $webmaster_email\r\n");
footer();
obExit();
}



#3 By: avenueb Subject:Optimizing the Datbase  Time: 05.Oct.05 03:43:12
Holy shit, I can't believe it,  it's exporting and then emailing my forum IM's to wiziwig     


#4 By: McJeroen Subject:Re:Optimizing the Datbase  Time: 06.Oct.05 12:23:07
Optimizing tables:

this is a function to clean out the database tables, to make them work better & faster.

you add some entries in a table (eg messages)...
you delete some...
you modify some...
you move some...

by doing all these things, the physical look of a table is like a big piece of paper (representing the table), with many white spaces on it from entries that have been deleted and so on...

By optimizing the tables, you let the server to re-sort the whole table to be accessible as fast as possible.
So, optimizing your database is something you better do one in a while.

Maybe this can be done automatically?
every X days the admin gets a nag-screen to optimize his tables...





#5 By: imagineopenes Subject:Optimizing the Datbase  Time: 06.Oct.05 14:08:03
Quote:
Holy s***, I can't believe it,  it's exporting and then emailing my forum IM's to wiziwig


lol no, nor do I think he'd want em


#6 By: webmaster Subject:Optimizing the Datbase  Time: 06.Oct.05 19:44:04
Well I um never gotten anything from you in my mail so whatever happened hasnt. Thanks McJ for explaining the functions.

Quote from: avenueb

Holy shit, I can't believe it,  it's exporting and then emailing my forum IM's to wiziwig     



#7 By: avenueb Subject:Optimizing the Datbase  Time: 07.Oct.05 03:16:24
thanks McJeroen!


#8 By: JasonMonette Subject:Re:Optimizing the Datbase  Time: 10.Feb.06 18:34:56
Auto optimization would be a good feature.  I would like to see that in future builds