What's This About?
In this Tutorial I'll give you a function called optimizeDatabase().
If you have a Database(s), you surelly issued overheads.
Run this script once per week (i.e. every Monday at midnight) and you will get rid of it.
Note: if you have huge amount of hits, then you should use it more often!
Function
<?
function optimizeDatabase($db, $connectToo = false, $username = 'root', $password = '', $host = 'localhost') {
if ($connectToo !== false) {
@mysql_connect($host, $username, $password) or die("Cannot connect to $host using username <strong>$username</strong>.");
@mysql_select_db($db) or die("Cannot pick database $db using username <strong>$username</strong>.");
}
$sqlGetTables = "SHOW TABLES FROM $db";
$qGetTables = @mysql_query($sqlGetTables) or die ("Cannot execute that query: ".mysql_error());
$num = 0;
$tables = array();
while ($f = mysql_fetch_row($qGetTables)) {
$tables[$num] = $f[0];
$num++;
}
$parse = implode('`, `', $tables);
$list = '`'.$parse.'`';
$optimize = @mysql_query("OPTIMIZE TABLE $list");
if ($optimize) {
echo "Your database $db has been successfully optimized!";
} else {
echo "I couldn't optimize your tables! Error: ".mysql_error();
}
@mysql_free_result($qGetTables);
if ($connectToo !== false) {
@mysql_close();
}
}
?>
How To Use It
Only one argument is reguired.
So use it thus:
optimizeDatabase('my_table');
That will optimize your table my_table.
Other Arguments
1) $db = the database you want to optimize
2) $connectToo = defaults to false. change it (to anything) if you want the script to connects to your DB firstly
3) $username = required if you used second argument - the username of your DB server
4) $password = required if you used second argument - the password of your DB server
5) $host = you can leave it as-is if your server is localhost; otherwise change it
Example (all arguments)
optimizeDatabase('site', true, 'nino', 'mypassword', 'localhost');
That will optimize table site but it will firstly connect to DB server localhost using username nino, password mypassword.
Example (two arguments)
optimizeDatabase('site', true);
This will probably work on your localhost if you didn't bother to configure it.
It will optimize database site and it WILL connect to DB firstly.
The username, password and server location will be default ('root', '', 'localhost')
Explanation
A brief one, if you want to know more about the functions, go to php.net's documentation.
1) We run a query which selects the names of all tables in a database
2) We create a new varialbe, $tables and that one is a array. This is optional, though, the array will be otherwise created automatically.
3) We run a while() loop (loops) which fills our array values.
4) When there is no more results, the loop ends. Table names are now in variable $table.
5) We use implode (its alias is join()) to change its shape. Now, those won't be in a array anymore, now they will be a string. Seperated by commas and ` (watch out, that isn't a single quote, ').
6) Final preparing step, we add that ` at the beggining and at the end of the string. Our SQL to optimize database is now in a variable $list.
7) We run the query and check if it worked. You can delete that to make your script silent though.
8) That's it!
Nino
ps.
sorry 1 - i wanted to post this 2 days but I lost my internet username
sorry 2 - i wanted to post this 1 day ago but I was on a party