MySQL OPTIMIZE all tables

Question

MySQL has an OPTIMIZE TABLE command which can be used to reclaim unused space in a MySQL install   Is there a way  built-in command or common stored procedure  to run this optimization for every table in the database and or server install  or is this something you d have to script up yourself

User · Answer

Following example php script can help you to optimize all tables in your database   lt  php  dbConnect      alltables   mysql query  SHOW TABLES     while   table   mysql fetch assoc  alltables        foreach   table as  db   gt   tablename              mysql query  OPTIMIZE TABLE     tablename             or die mysql error                gt

User · Answer

Do all the necessary procedures for fixing all tables in all the databases with a simple shell script      bin bash mysqlcheck --all-databases mysqlcheck --all-databases -o mysqlcheck --all-databases --auto-repair mysqlcheck --all-databases --analyze

User · Answer

If you are accessing database directly then you can write following query   OPTIMIZE TABLE table1 table2 table3 table4

User · Answer

If you want to analyze  repair and optimize all tables in all databases in your MySQL server  you can do this in one go from the command line  You will need root to do that though   mysqlcheck -u root -p --auto-repair --optimize --all-databases   Once you run that  you will be prompted to enter your MySQL root password  After that  it will start and you will see results as it s happening   Example output   yourdbname1 yourdbtable1       OK yourdbname2 yourdbtable2       Table is already up to date yourdbname3 yourdbtable3 note       Table does not support optimize  doing recreate   analyze instead status     OK  etc   etc     Repairing tables yourdbname10 yourdbtable10 warning    Number of rows changed from 121378 to 81562 status     OK   If you don t know the root password and are using WHM  you can change it from within WHM by going to   Home   SQL Services   MySQL Root Password

User · Answer

A starter bash script to list and run a tool against the DBs         bin bash  declare -a dbs unset opt  for each in   echo  show databases     mysql -u root   do          dbs    each   done    echo   The system found      dbs       databases    sleep 2 echo echo  press 1 to run a check  echo  press 2 to run an optimization  echo  press 3 to run a repair  echo  press 4 to run check repair  and optimization  echo  press q to quit  read input  case  input in         1  opt  -c                     2  opt  -o                     3  opt  -r                     4  opt  --auto-repair -c -o                        echo  Quitting Application      exit 7            esac     -z  opt     amp  amp  exit 7   echo   running option   mysqlcheck  opt in 5 seconds  on all Dbs       sleep 5  for   i 0  i lt    dbs      i      do         echo    dbs  i               mysqlcheck  opt   dbs  i    -u root     done

User · Answer

This bash script will accept the root password as option and optimize it one by one  with status output      bin bash  if   -z   1      then   echo   echo  ERROR  root password Parameter missing     exit fi MYSQL USER root MYSQL PASS  1 MYSQL CONN  -u  MYSQL USER  -p  MYSQL PASS   TBLLIST    COMMA    SQL  SELECT CONCAT table schema     table name  FROM information schema tables WHERE  SQL    SQL  table schema NOT IN   information schema   mysql   performance schema    for DBTB in  mysql   MYSQL CONN  -ANe   SQL    do     echo OPTIMIZE TABLE    DBTB        SQL  OPTIMIZE TABLE   DBTB        mysql   MYSQL CONN  -ANe   SQL   done

User · Answer

You can optimize check and repair all the tables of database  using mysql client   First  you should get all the tables list  separated with       mysql -u USERNAME  -p PASSWORD  -Bse  show tables   DB NAME  xargs perl -pe  s     g    Now  when you have all the tables list for optimization   mysql -u USERNAME  -p PASSWORD  -Bse  optimize tables  tables list    DB NAME

User · Answer

my 1 cent  added and TABLE TYPE  BASE TABLE  so we can skip the  VIEW  type  for table in  mysql -sss -e  quot select concat table schema     table name  from information schema tables where table schema not in   mysql   information schema   performance schema   and TABLE TYPE  BASE TABLE  order by data free desc  quot   do mysql -e  quot OPTIMIZE TABLE  table  quot  done

User · Answer

The MySQL Administrator  part of the MySQL GUI Tools  can do that for you on a database level   Just select your schema and press the Maintenance button in the bottom right corner   Since the GUI Tools have reached End-of-life status they are hard to find on the mysql page  Found them via Google  http   dev mysql com downloads gui-tools 5 0 html  I don t know if the new MySQL Workbench can do that  too   And you can use the mysqlcheck command line tool which should be able to do that  too

User · Answer

You can use mysqlcheck to do this at the command line   One database   mysqlcheck -o  lt db schema name gt    All databases   mysqlcheck -o --all-databases

User · Answer

my 2cents  start with table with highest fragmentation   for table in  mysql -sss -e  select concat table schema     table name  from information schema tables where table schema not in   mysql   information schema   performance schema   order by data free desc   do mysql -e  OPTIMIZE TABLE  table   done

User · Answer

From command line   mysqlcheck -o  lt db name gt  -u lt username gt  -p   then type password

User · Answer

I made this  simple  script   set  tables like   null  set  optimize   null  set  show tables   concat  show tables where   ifnull concat    Tables in    database       like      tables like     and             optimize  concat ws      optimize  Tables in    database             Prepare  bd  from  show tables  EXECUTE  bd   DEALLOCATE PREPARE  bd    set  optimize    concat  optimize table     optimize   PREPARE  sql  FROM  optimize  EXECUTE  sql   DEALLOCATE PREPARE  sql    set  show tables   null   optimize   null   tables like   null    To run it  simply paste it in any SQL IDE connected to your database   Notice  this code WON T work on phpmyadmin   How it works  It runs a show tables statement and stores it in a prepared statement  Then it runs a optimize table in the selected set   You can control which tables to optimize by setting a different value in the var  tables like  e g   set  tables like     test

User · Answer

for all databases   mysqlcheck -Aos -uuser -p    For one Database optimization   mysqlcheck -os -uroot -p dbtest3

User · Answer

From phpMyAdmin and other sources you can use   SET SESSION group concat max len   99999999  SELECT GROUP CONCAT concat  OPTIMIZE TABLE     table name        SEPARATOR     AS O FROM INFORMATION SCHEMA TABLES WHERE  TABLE TYPE    BASE TABLE  AND table name   dual  AND TABLE SCHEMA     lt your databasename gt     Then you can copy  amp  paste the result to a new query or execute it from your own source  If you don t see the whole statement

[mysql] MySQL OPTIMIZE all tables?

Examples related to mysql

Examples related to sql