00:00
00:00
Newgrounds Background Image Theme

FloofGaming just joined the crew!

We need you on the team, too.

Support Newgrounds and get tons of perks for just $2.99!

Create a Free Account and then..

Become a Supporter!

Php: Database Optimization

2,663 Views | 14 Replies
New Topic Respond to this Topic

Php: Database Optimization 2006-08-27 09:48:53


PHP: Main

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

Response to Php: Database Optimization 2006-08-27 16:27:07


So, I've read throught this a few times...and I'm not getting exactly how it optimizes the database. Could just be lack of sleep, but could you explain more?

Response to Php: Database Optimization 2006-08-27 16:36:54


At 8/27/06 04:27 PM, WoogieNoogie wrote: So, I've read throught this a few times...and I'm not getting exactly how it optimizes the database. Could just be lack of sleep, but could you explain more?

It's a intergrated SQL command OPTIMIZE TABLE. It removes the overheard. You can that son of a bitch in phpMyadmin.

It removes all free space from a table and organize the data more efficiently.

Like when you are defragmanting your hard disks. Same shit

Response to Php: Database Optimization 2006-08-27 16:38:33


Optimizing is only needed if you insert/delete quite a lot, right?


"My software never has bugs. It just develops random features. " - Unknown

[ FluxBB developer | Quickmarks 0.5.1 | Strings & Ints - my blog ]

BBS Signature

Response to Php: Database Optimization 2006-08-27 16:38:43


At 8/27/06 04:36 PM, Nino_JoJ wrote: It's a intergrated SQL command OPTIMIZE TABLE. It removes the overheard. You can that son of a bitch in phpMyadmin.

Now this two sentences are a nonsense so I'll remaster them:

It's intergrated SQL command OPTIMIZE TABLE. It removes the OVERHEAD. You can see that son of a bitch in phpMyAdmin.

Response to Php: Database Optimization 2006-08-27 16:42:21


At 8/27/06 04:38 PM, elbekko wrote: Optimizing is only needed if you insert/delete quite a lot, right?

Not really.
My whoIsOnline counter, even though it is better than ever, makes overhead on table even if it refreshes only like 10 times.

It's weird.

Response to Php: Database Optimization 2006-08-27 16:48:07


Well, well, after looking through phpMyAdmin, I see how useful this is.

Response to Php: Database Optimization 2006-08-27 16:52:34


At 8/27/06 04:48 PM, WoogieNoogie wrote: Well, well, after looking through phpMyAdmin, I see how useful this is.

Yes, but this one is more automatic.
You instantly optimize the whole DB.

And you can run it when you want, regardless of phpMyAdmin.

Response to Php: Database Optimization 2006-08-27 17:04:11


Okay, this worked, but I think you were using the terms "table" and "database" incorrectly. For example...

optimizeDatabase('my_table');
That will optimize your table my_table.

That would optimize your DATABASE named "my_table".

Response to Php: Database Optimization 2006-08-27 17:22:09


At 8/27/06 05:04 PM, WoogieNoogie wrote: Okay, this worked, but I think you were using the terms "table" and "database" incorrectly. For example...

optimizeDatabase('my_table');
That will optimize your table my_table.
That would optimize your DATABASE named "my_table".

Yes, correct, my bad

Response to Php: Database Optimization 2006-08-27 17:27:55


At 8/27/06 05:22 PM, Nino_JoJ wrote: Yes, correct, my bad

very nice. i might write up a tutorial for my full database management/maintenance system


BBS Signature

Response to Php: Database Optimization 2007-08-23 17:06:37


Sorry for raising the dead but I'm confused.

This removes all the spaces in your database right? But what about the spaces you need? Does it erase them?

Can somebody explain this more?

Response to Php: Database Optimization 2007-08-23 20:55:01


At 8/23/07 05:06 PM, Seachmall wrote: Sorry for raising the dead but I'm confused.

This removes all the spaces in your database right? But what about the spaces you need? Does it erase them?

Can somebody explain this more?

it doesn't remove all the 'spaces,' it more or less removes wasted space, and cleans everything up for you.


BBS Signature

Response to Php: Database Optimization 2007-08-24 06:11:56


Basically, it will remove entries that basically read as "blank" and delete the sectors for that data. It does nothing to the table structure, so data can still be written at a later time for the appropriate category. All it does is reduce the number of zeros in the database.


-Eulif Davis-

Response to Php: Database Optimization 2007-08-24 09:13:19


It defragments the empty space.


Each time someone abuses hittest, God kills a kitten. Please, learn real collision testing.