optimizedb.sh

gistfile1.txt

#!/bin/sh

## When the Senior School Database was overloading some tables and we weren't sure why we have to optimize the DB every day. 
## This was the code I used. 

mysql -e "SELECT TABLE_NAME FROM (SELECT TABLE_SCHEMA,TABLE_NAME,Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 2) 'TABLEsizeGB' FROM information_schema.tables WHERE TABLE_SCHEMA='seniorschool' GROUP BY TABLE_SCHEMA,TABLE_NAME HAVING Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 2) > 0.05 ORDER BY TABLEsizeGB DESC) as A;" | while read TABLE_NAME; do
	if [ $TABLE_NAME != 'TABLE_NAME' ]
	then
		mysqlcheck --optimize seniorschool $TABLE_NAME
	fi
done