Optimize MySQL Databases in Plesk

This article goes over mass optimziation of MySQL databases and tables.  This can be done in a per database basis or to all databases.

1. Open an SSH connection to the server and log in as the root user. 
2. Once logged in as the root user, create a file name optimize.sh with the following data:

#!/bin/sh
DBNAME=$2
if [ -e /etc/psa/.psa.shadow ]; then
	PLESKJAWNS="-uadmin -p`cat /etc/psa/.psa.shadow`"
else
	PLESKJAWNS=""
fi
 
printUsage() {
 echo "Usage: $0"
 echo " --optimize <dbname>"
 echo " --optimizeall"
 echo " --repair <dbname>"
 echo " --repairall"
 return
}
doAllTables() {
 # get the table names
 TABLENAMES=`mysql $PLESKJAWNS -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`
 # loop through the tables and optimize them
 for TABLENAME in $TABLENAMES
 do
   mysql $PLESKJAWNS -D $DBNAME -e "$DBCMD TABLE $TABLENAME;"
 done
}
doAllDatabases() {
 # get the database names
 DATABASES=`mysql $PLESKJAWNS -e "Show Databases" | grep -v + | grep -v psa`
 for DATABASE in $DATABASES
 do
   # get the table names
   TABLENAMES=`mysql $PLESKJAWNS -D $DATABASE -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`
   # loop through the tables and optimize them
   for TABLENAME in $TABLENAMES
   do
     mysql $PLESKJAWNS -D $DATABASE -e "$DBCMD TABLE $TABLENAME;"
   done
 done
 }
if [ $# -eq 0 ] ; then
 printUsage
 exit 1
fi
case $1 in
 --optimize) DBCMD=OPTIMIZE; doAllTables;;
 --optimizeall) DBCMD=OPTIMIZE; doAllDatabases;;
 --repair) DBCMD=REPAIR; doAllTables;;
 --repairall) DBCMD=REPAIR; doAllDatabases;;
 --help) printUsage; exit 1;;
 *) printUsage; exit 1;;
esac

3. Update the permissions on the file using the following command:
chmod +x optimize.sh
4. To repair and optimize a single database, run the following commands:
./optimize.sh --repair DBNAME./optimize.sh --optimize DBNAME
5. To repair and optimize a all databases, run the following commands:
./optimize.sh --repairall./optimize.sh --optimizeall