OPTIMIZE TABLE

Performs optimizations on the storage of rows in a table.

Optimizing Columnstore Tables

This topic assumes familiarity with the concepts of columnstore table layout, including segments, row segment groups, and background mergers. For more on these topics, see Managing Columnstore Segments.

MemSQL automatically runs optimization routines on tables, but they can also be started manually.

Syntax

OPTIMIZE TABLE table_name [FULL | FLUSH]
Warning

This command can only be run against one table at a time. See Deprecations for more details.

Arguments

The types of optimization performed by OPTIMIZE TABLE are discussed below.

Remarks

There are a few types of optimization performed on columnstore tables:

See Managing Columnstore Segments for details.

Optimizing Rowstore Tables

For rowstore tables, OPTIMIZE TABLE physically sorts the data in memory by the primary key and optimizes the organization of the table’s internal structures, so primary key table scans will execute significantly faster. However, this benefit diminishes as writes are made to the table. It also triggers garbage collection on the table.

Syntax

OPTIMIZE TABLE table_name

Arguments

Was this article useful?