Skip to content

Instantly share code, notes, and snippets.

@fredbradley
Created August 10, 2023 08:08
Show Gist options
  • Save fredbradley/ce66f20e51d65a4b3bd1a406e5fe743b to your computer and use it in GitHub Desktop.
Save fredbradley/ce66f20e51d65a4b3bd1a406e5fe743b to your computer and use it in GitHub Desktop.
optimizedb.sh
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment