Wondering how to Optimize table in mariaDB? Our MySQL Support team is here to lend a hand with your queries and issues.

How to Optimize table in mariaDB?

Today, let us see the steps followed by our support techs to optimize table

Syntax:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] … [WAIT n | NOWAIT]
OPTIMIZE TABLE has two main functions.

First and foremost, it can either used to defragment tables, or to update the InnoDB fulltext index.

Defragmenting

OPTIMIZE TABLE works for InnoDB , Aria, MyISAM and ARCHIVE tables.

And should use if you have delete a large part of a table or if you have made many changes to a table with variable-length rows.

Then, delete rows are maintain in a link list and subsequent INSERT operations reuse old row positions.

This statement requires SELECT and INSERT privileges for the table.

By default, OPTIMIZE TABLE statements are written to the binary log and will replicate.

The NO_WRITE_TO_BINLOG keyword (LOCAL is an alias) will ensure the statement is not written to the binary log.

From MariaDB 10.3.19, OPTIMIZE TABLE statements are not log to the binary log if read_only is set.

You can use ALTER TABLE ... OPTIMIZE PARTITION to optimize one or more partitions.

Also, you can use OPTIMIZE TABLE to reclaim the unuse space and to defragment the data file.

Then, with other storage engines, OPTIMIZE TABLE does nothing by default, and returns this message: ” The storage engine for the table doesn’t support optimize”.

However, if the server has start with the --skip-new option, OPTIMIZE TABLE is link to ALTER TABLE, and recreates the table.

Finally, this operation frees the unuse space and updates index statistics.

Updating an InnoDB fulltext index

When rows are added or deleted to an InnoDB fulltext index, the index is not immediately re-organized, as this can be an expensive operation.

Change statistics are stored in a separate location . The fulltext index is only fully re-organized when an OPTIMIZE TABLE statement is run.

By default, an OPTIMIZE TABLE will defragment a table.

In order to use it to update fulltext index statistics, the innodb_optimize_fulltext_only system variable must be set to 1.

This is intend to a temporary setting, and should be reset to 0 once the fulltext index has re-organize.

Since fulltext re-organization can take a long time, the innodb_ft_num_word_optimize variable limits the re-organization to a number of words.

You can run multiple OPTIMIZE statements to fully re-organize the index.

Defragmenting InnoDB tablespaces

MariaDB 10.1.1 merged the Facebook/Kakao defragmentation patch, allowing one to use OPTIMIZE TABLE to defragment InnoDB tablespaces.

For this functionality to enabled, the innodb_defragment system variable must be enabled.

No new tables are create and there is no need to copy data from old tables to new tables.

Instead, this feature loads n pages and tries to move records so that pages would be full of records and then frees pages that are fully empty after the operation.

Note that tablespace files will not shrink as the result of defragmentation, but one will get better memory utilization in the InnoDB buffer pool as there are fewer data pages in use.

See below how to use OPTIMIZE TABLE :

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;

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

To sum up, our Support Engineers demonstrated how to Optimize table in mariaDB

.