Squarebox

Optimizing Tables in MySQL

Over time MySql  can become increasingly slow, running regular housekeeping from the tools option of the CatDV Server Control Panel can help  but this does  not optimize the tables of the  MySql  Database

this can be done with a simple script from the command line

Before performing this operation which can potentially making non-reversible changes to the database it is vitally important that you perform a complete backup of the catdv database. see our guide to backing up  CatDV for more info 

First copy the following text into a text editor and save as a  text file with name  optimize_tables.sql

####################################################################################

OPTIMIZE TABLE c_group;

OPTIMIZE TABLE c_permission;

OPTIMIZE TABLE c_role;

OPTIMIZE TABLE c_roleperm;

OPTIMIZE TABLE c_user;

OPTIMIZE TABLE catalog;

OPTIMIZE TABLE clip;

OPTIMIZE TABLE clipTypes;

OPTIMIZE TABLE clipUsage;

OPTIMIZE TABLE event;

OPTIMIZE TABLE folder;

OPTIMIZE TABLE folderMember;

OPTIMIZE TABLE importSource;

OPTIMIZE TABLE maxid;

OPTIMIZE TABLE metaclip;

OPTIMIZE TABLE settings;

OPTIMIZE TABLE smartFolder;

OPTIMIZE TABLE sourceMedia;

OPTIMIZE TABLE tapeInfo;

OPTIMIZE TABLE thumbnail;

OPTIMIZE TABLE userData;

OPTIMIZE TABLE version;

####################################################################################

then  run the following  from a terminal or command prompt

mysql -u catdv -p catdv < optimize_tables.sql

this will optimize the tables in your Database – for large Databases with regular changes its worth scheduling  this operation regularly  say once a month, for how to run housekeeping and empty the CatDV recycle bin see Perform Housekeeping