Hi all, I'm in the process of setting up a cron job to automatically optimize the database once or twice a day. Does anyone know if the current Optimize Database command under; Administration>Database in the Admin is only optimizing the tables, or does it also repair? Lastly, is it doing all tables as far as we know?
Many have noted and commented in the past how CS Cart performs a lot better, pages load faster, for a period of time after the optimize command has been run.
I'm going to set our store so that it happens automatically, but I'm hesitant to do it too often due to the db being locked whilst this command runs. (otherwise, Id probably do it every single hour lol)
Another thought; if someone knows which php file contains the script thats already running the Admin optimize db command, I could copy that into a new php file and point my server cron job to that instead.
Otherwise, i'll make do with this one; cronjob to optimize MySQL database - SQL Snipplr Social Repository
Found the file; skins/basic/admin/views/database/manage.tpl
Now to sort through the code…
hmm…
```php
{include file="buttons/button.tpl" but_text=$lang.optimize_database but_name="dispatch[database.optimize]" but_role="button_main"}
```
Not much there. Looks like a form submit.
Beyond me..
app/controllers/backend/database.php
if ($mode == 'optimize') {
// Log database optimization
fn_log_event('database', 'optimize');
$all_tables = db_get_fields("SHOW TABLES");
fn_set_progress('parts', sizeof($all_tables));
foreach ($all_tables as $table) {
fn_set_progress('echo', __('optimizing_table') . " $table...
");
db_query("OPTIMIZE TABLE $table");
db_query("ANALYZE TABLE $table");
$fields = db_get_hash_array("SHOW COLUMNS FROM $table", 'Field');
if (!empty($fields['is_global'])) { // Sort table by is_global field
fn_echo('.');
db_query("ALTER TABLE $table ORDER BY is_global DESC");
} elseif (!empty($fields['position'])) { // Sort table by position field
fn_echo('.');
db_query("ALTER TABLE $table ORDER BY position");
}
}
fn_set_notification('N', __('notice'), __('done'));
}
return array(CONTROLLER_STATUS_OK, "database.manage");
}
Thank you! I knew it would be buried in a php file somewhere.
Do note that an optimize if very expensive (copies tables, recreates indexes) and is also blocking. So while you're optimizing a couple of times a day, your customers will not be able to do anything until the optimize completes. The larger the table, the time to optimize grows logarithmically.
Thanks Tony. I realise the db will be locked during this operation, that's why I plan to do it twice a day at most.
Oh my, is there really a need to optimize the database twice a day? Never really thought about doing it that much before.
I think so. I'm currently on 2.2.4 and notice my page loads improve by a couple of seconds after the optimize db command is run. If nothing else, setting it as a cron job to run from time to time at least keeps your db size down and running more efficiently.
A friend of mine on V3 of Cs Cart yesterday confirmed his page loads have improved by around 2 seconds a page.
I think anything more than once a week is overkill IMO. Guess it also depends on how many transactions you are doing per day/week and to analyse your traffic to ensure the optimisation is executed at the quietest time of day to prevent disruption to your customers.
The optimization appears to take less than 3 seconds in the Admin, but I agree; we certainly don't want to lock customers out of the database.
The thing is, we notice an immediate benefit, and after a few hours, the site starts returning to its old ways; anywhere from 3 - 6 second page loads. Immediately after the optimization, our pages loads are between 1 and 2 seconds.
I can safely set the cron to run around 3am to 4am to minimise disruption, perhaps once every 24 hours will have to be enough. like I said in the first post though, if I could run it every hour I would. The difference is that noticeable.
I don't know about yall but whenever I run the maintenance in admin, it takes no longer than 10 seconds so I am not sure why the concern with the database being unavailable.?
Here is a cron/command line that will use the built in function for optimizing the database without messing with creating scripts.
mysqlcheck -Bao [db_name] -u [user] -p[password] > /dev/null
If you only have one database or you want to optimize all of your databases, you can just use this.
mysqlcheck -Aao -u [user] -p[password] > /dev/null
Edit: Source
You rock. Thank you.
Then a 'ps' will reveal the db user and the db password.
Rarely a good idea to specify these on the command line. Should use .my.cnf file instead for the account user.
Of course, only a security risk if your site has already been hacked, but why expose it when you don't have to?
The time it takes is site (db) dependent.
All a db OPTIMIZE_TABLE does is removes the unused space within a table (compacts it) and rebuilds the indexes. The cs-cart internal version (and the one in our EZ Admin Helper product) will ensure that various sort orders (is_global columns and position columns) are ordered for the greatest optimization for the cart.
Tony, that Add-on looks brilliant. You should’ve mentioned it at the start 
I’m currently on version 2.2.4 but will buy this Add-on once we finish our migration to the latest 4x.
I like the idea of the sort orders being performed, I noticed the inbuilt CS Cart function did that too. But at least your add-on will let me automate the whole process.