Optimize DB

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Optimize DB

Post by s.dot »

Is it possible to optimize database tables in a PHP script?

Would it be safe to put an optimizing code on the main page of my site, if it's possible?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you mean calling "OPTIMIZE `table_name`" ? It's a normal query, so yes.. Running an optimize all the time, or often isn't probably the best idea.. as it'll slow down any page request... maybe create a little check that sees if it's far enough out of whack that optimizing would be "beneficial" and then run it.. or only manually do it from the admin area.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

...

Post by s.dot »

Doing it manually is getting to be a bit of a hassle
I have 20 tables that are constantly added to and deleted from

what would I check for to see if it's "too out of whack"?

Like the size of the overhead? If so, how would I check for the size of the overhead?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SHOW TABLE STATUS LIKE 'table_name'
field: Data_free (value in bytes) notice that the table's name is in single quotes.

that should be compared to the size of the table, which is the sum of Data_length and Index_length
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

...

Post by s.dot »

That's still above my head

could you show me an example in coding, if it's not too much of your time
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

play around with it.. calculate the percentage of overhead, choose an acceptable maximum percentage. If the calculated percentage is over this theoretic maximum, then optimize. Otherwise it's not worth the time spent with a lock on it.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

K, so I just played around with this.. and I have this code

Code: Select all

<?
require 'important.php'; // contains database connection, and selection

$sql = "OPTIMIZE `friends`";

mysql_query($sql) or die(mysql_error());

echo "Table 'friends' has been optimized.";
?>
I really don't know what I'm doing here. I get this error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'friends`' at line 1.

Since feyd said it was a query, I tried that. Since I also looked up in PHP.net an optimize function. And it doesn't exist. dba_optimize exists but the wording they use is foreign to me, and there are no user contributed notes.

Any help?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

OPTIMIZE TABLE `table_name`
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Thanks very much feyd. I feel we've disagreed on many things already on this board, and I'm a n00b. But I have much respect for your knowledge. :P
Post Reply