Page 1 of 1
Optimize DB
Posted: Wed Feb 09, 2005 11:34 pm
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?
Posted: Wed Feb 09, 2005 11:38 pm
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.
...
Posted: Wed Feb 09, 2005 11:46 pm
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?
Posted: Wed Feb 09, 2005 11:52 pm
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
...
Posted: Wed Feb 09, 2005 11:58 pm
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
Posted: Thu Feb 10, 2005 12:20 am
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.
Posted: Thu Feb 17, 2005 9:56 am
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?
Posted: Thu Feb 17, 2005 10:17 am
by feyd
Posted: Thu Feb 17, 2005 2:18 pm
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.
