Optimize DB
Moderator: General Moderators
Optimize DB
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?
Would it be safe to put an optimizing code on the main page of my site, if it's possible?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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.
...
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?
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?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
SHOW TABLE STATUS LIKE 'table_name'that should be compared to the size of the table, which is the sum of Data_length and Index_length
K, so I just played around with this.. and I have this code
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?
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.";
?>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?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Code: Select all
OPTIMIZE TABLE `table_name`