How to optimize MySQL/MariaDB tables

The commands from this post will work on MySQL and MariaDB server.

It’s a good idea to perform database maintenance from time to time. One thing is to do is to optimize the tables. We have two options:

1. OPTIMIZE TABLE command

Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.

See below how to use it.

root@web [~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3670
Server version: 10.1.22-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use roundcube
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [roundcube]> OPTIMIZE TABLE cache;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| roundcube.cache | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| roundcube.cache | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.04 sec)

MariaDB [roundcube]> quit
Bye
root@web [~]#

If you want to run the command for multiple tables from the same database, use:

OPTIMIZE TABLE table1,table2,table3;
OPTIMIZE TABLE works with InnoDB, MyISAM, and ARCHIVE tables.

2. mysqlcheck command

The mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

To check one table use: mysqlcheck db_name tbl_name
To check all tables from a database: mysqlcheck –databases db_name
To check the tables from all the databases on the server: mysqlcheck –all-databases

Notice that database tables are locked while mysqlcheck is running. No records can be inserted or deleted from the tables.
root@web [~]# mysqlcheck roundcube
roundcube.cache                                    OK
roundcube.cache_index                              OK
roundcube.cache_messages                           OK
roundcube.cache_shared                             OK
roundcube.cache_thread                             OK
roundcube.contactgroupmembers                      OK
roundcube.contactgroups                            OK
roundcube.contacts                                 OK
roundcube.cp_schema_version                        OK
roundcube.dictionary                               OK
roundcube.identities                               OK
roundcube.searches                                 OK
roundcube.session                                  OK
roundcube.system                                   OK
roundcube.users                                    OK
root@web [~]# 

To optimize a database, use:

root@web [~]# mysqlcheck -o roundcube
roundcube.cache
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_index
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_messages
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_shared
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cache_thread
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contactgroupmembers
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contactgroups
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.contacts
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.cp_schema_version                        Table is already up to date
roundcube.dictionary
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.identities
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.searches
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.session
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.system
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
roundcube.users
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
root@web [~]#

To optimize all the database on the sever use:

root@web [~]# mysqlcheck -o -A

Resources:
OPTIMIZE TABLE manual
mysqlcheck manual

Share this post: