All pages
Powered by GitBook
1 of 11

Table Statements

Learn about table-related SQL statements in MariaDB Server. This section covers commands for creating, altering, dropping, and manipulating tables, essential for managing your database schema.

ANALYZE TABLE

Syntax

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [,tbl_name ...]
  [PERSISTENT FOR 
    { ALL
      | COLUMNS ([col_name [,col_name ...]]) INDEXES ([index_name [,index_name ...]])
    }
  ]

Description

ANALYZE TABLE analyzes and stores the key distribution for a table (index statistics). This statement works with MyISAM, Aria and InnoDB tables. During the analysis, InnoDB will allow reads/writes, and MyISAM/Aria reads/inserts. For MyISAM tables, this statement is equivalent to using myisamchk --analyze.

ANALYZE uses histograms, which can provide a better selectivity than InnoDB statistics offer. InnoDB statistics work with a limited sample set and is therefore not as accurate as persistent statistics can be. For more information on how the analysis works within InnoDB, seeInnoDB Limitations.

MariaDB uses the stored key distribution to decide the order in which tables should be joined when you perform a join on something other than a constant. In addition, key distributions can be used when deciding which indexes to use for a specific table within a query.

This statement requires SELECT and INSERT privileges for the table.

By default, ANALYZE TABLE statements are written to the binary log and will be replicated. The NO_WRITE_TO_BINLOG keyword (LOCAL is an alias) will ensure the statement is not written to the binary log.

ANALYZE TABLE statements are not logged to the binary log if read_only is set. See also Read-Only Replicas.

ANALYZE TABLE is non-blocking and non-intrusive. A connection will start using new statistics for the query following the completion of the ANALYZE TABLE.

ANALYZE TABLE is blocking and intrusive.

ANALYZE TABLE is also supported for partitioned tables. You can use ALTER TABLE ... ANALYZE PARTITION to analyze one or more partitions.

The Aria storage engine supports progress reporting for the ANALYZE TABLE statement.

Performance Impact

Note that analyzing tables with ANALYZE can have a performance impact and can use a lot of disk space for big tables. As column statistics usually do not change much over time, even when the table grows, there is no benefit to running ANALYZE very often.

Running ANALYZE is indicated:

  • for newly populated tables,

  • for tables that have additional columns added that are used in WHERE clauses,

  • when a table has doubled in size,

  • when you note that a query becomes slow because the table order has changed and you can see from EXPLAIN or ANALYZE FORMAT=JSON that the selectivity is wrong for a table.

ANALYZE isn’t useful for table columns of type UNIQUE, PRIMARY KEY, TIME, or CURRENT_TIME. In ANALYZE queries, you should omit columns of those types.

Engine-Independent Statistics / PERSISTENT FOR

ANALYZE TABLE supports engine-independent statistics. See Engine-Independent Table Statistics: Collecting Statistics with the ANALYZE TABLE Statement for more information.

Engine-independent statistics can be controlled (enabled and disabled) using the use_stat_tables variable and the optimizer_use_condition_selectivity variable. InnoDB-persistent statistics are controlled with the innodb_stats_persistent variable. Combining both kinds of statistics is possible.

The server relies on InnoDB statistics by default. That way, it can use some statistics even if ANALYZE TABLE is never run (or not often enough). This gives good enough results for the majority of queries. Some queries, however, need more statistical data so the optimizer can create a good plan. Slow queries indicate there aren't enough statistical data. Those queries can be accelerated by running ANALYZE TABLE tbl PERSISTENT FOR ..., where tbl indicates a table used by a slow query. You can also run ANALYZE TABLE ... PERSISTENT FOR ALL, but that has a significant performance impact.

Useful Variables

The following overview indicates when a particular variable was introduced. When multiple versions are given, it means variable options (like the default value) changed between the indicated versions.

Variable
Introduced in MariaDB version
Description

analyze_sample_percentage

10.4.3

Percentage of rows from the table ANALYZE TABLE will sample to collect table statistics. Set to 0 to let MariaDB decide what percentage of rows to sample.

histogram_type

10.4.3-11.0

Specifies the type of histograms created by ANALYZE. Options are #SINGLE_PREC_HB,DOUBLE_PREC_HB or JSON_HB.

histogram_size

10.7

Number of bytes or buckets (in case of JSON_HB) used for storing the histogram. If set to 0, no histograms are created by ANALYZE.]]

log_slow_admin_statements

11.0.1

Log slow OPTIMIZE, ANALYZE, ALTER and other administrative statements to the slow log if it is open. Deprecated. Use log_slow_filter instead.

log_slow_filter

(all versions)

Having admin in log_slow_filter will add slow ANALYZE_TABLE statements to the slow log.

sort_buffer_size

(all versions)

For calculating the number of duplicates, ANALYZE TABLE uses a buffer of sort_buffer_size bytes per column. You can slightly increase the speed of ANALYZE TABLE by increasing this variable.

Examples

-- update all engine-independent statistics for all columns and indexes
ANALYZE TABLE tbl PERSISTENT FOR ALL;

-- update specific columns and indexes:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES (idx1,idx2,...);

-- empty lists are allowed:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS (col1,col2,...) INDEXES ();
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES (idx1,idx2,...);

-- the following will only update mysql.table_stats fields:
ANALYZE TABLE tbl PERSISTENT FOR COLUMNS () INDEXES ();

-- when use_stat_tables is set to 'COMPLEMENTARY' or 'PREFERABLY', 
-- a simple ANALYZE TABLE  collects engine-independent statistics for all columns and indexes.
SET SESSION use_stat_tables='COMPLEMENTARY';
ANALYZE TABLE tbl;

See Also

  • This one trick can make MariaDB 30x faster! (mariadb.org blog)

  • Index Statistics

  • InnoDB Persistent Statistics

  • Progress Reporting

  • Engine-independent Statistics

  • Histogram-based Statistics

  • ANALYZE Statement

This page is licensed: GPLv2, originally from fill_help_tables.sql

CHECK TABLE

Syntax

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

Description

CHECK TABLE checks a table or tables for errors. CHECK TABLE works for Archive, Aria, CSV, InnoDB, MyISAM and, from MariaDB 12.0, Sequence, tables. For Aria and MyISAM tables, the key statistics are updated as well. For CSV, see also Checking and Repairing CSV Tables.

As an alternative, myisamchk is a commandline tool for checking MyISAM tables when the tables are not being accessed. For Aria tables, there is a similar tool: aria_chk.

For checking dynamic columns integrity, COLUMN_CHECK() can be used.

CHECK TABLE can also check views for problems, such as tables that are referenced in the view definition that no longer exist.

CHECK TABLE is also supported for partitioned tables. You can use ALTER TABLE ... CHECK PARTITION to check one or more partitions.

The meaning of the different options are as follows - note that this can vary a bit between storage engines:

Option
Explanation

FOR UPGRADE

Do a very quick check if the storage format for the table has changed so that one needs to do a REPAIR. This is only needed when one upgrades between major versions of MariaDB or MySQL. This is usually done by running mariadb-upgrade.

FAST

Only check tables that has not been closed properly or are marked as corrupt. Only supported by the MyISAM and Aria engines. For other engines the table is checked normally

CHANGED

Check only tables that has changed since last REPAIR / CHECK. Only supported by the MyISAM and Aria engines. For other engines the table is checked normally.

QUICK

Do a fast check. For MyISAM and Aria, this means skipping the check of the delete link chain, which may take some time.

MEDIUM

Scan also the data files. Checks integrity between data and index files with checksums. In most cases this should find all possible errors.

EXTENDED

Does a full check to verify every possible error. For InnoDB, Aria, and MyISAM, verify for each row that all its keys exists, and for those index keys, they point back to the primary clustered key. This may take a long time on large tables.

The EXTENDED option is available to InnoDB, too.

The EXTENDED option is ignored by InnoDB.

When the EXTENDED option is given, MariaDB also checks for referential integrity.

MariaDB does not check for referential integrity, even if the EXTENDED option is given.

For most cases, running CHECK TABLE without options or MEDIUM should be good enough.

The Aria storage engine supports progress reporting for this statement.

If you want to know if two tables are identical, take a look at CHECKSUM TABLE.

InnoDB

If CHECK TABLE finds an error in an InnoDB table, MariaDB might shutdown to prevent the error propagation. In this case, the problem will be reported in the error log. Otherwise the table or an index might be marked as corrupted, to prevent use. This does not happen with some minor problems, like a wrong number of entries in a secondary index. Those problems are reported in the output of CHECK TABLE.

Each tablespace contains a header with metadata. This header is not checked by this statement.

During the execution of CHECK TABLE, other threads may be blocked.

Examples

CHECK TABLE y EXTENDED;
+--------+-------+----------+----------+
| Table  | Op    | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.y | check | status   | OK       |
+--------+-------+----------+----------+

In newer MariaDB versions, the EXTENDED clause provides more information:

CHECK TABLE t1 EXTENDED;
+---------+-------+----------+----------------------------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                                             |
+---------+-------+----------+----------------------------------------------------------------------+
| test.t1 | check | Warning  | No suitable key found for foreign key t2_ibfk_1 in table test.t1    |
+---------+-------+----------+----------------------------------------------------------------------+

CHECK TABLE t2 EXTENDED;
+---------+-------+----------+--------------------------------------------------------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                                                                         |
+---------+-------+----------+--------------------------------------------------------------------------------------------------+
| test.t2 | check | status   | Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2')  |
| test.t2 | check | status   | Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '2')  |
| test.t2 | check | status   | Cannot add or update a child row: a foreign key constraint fails (Key: t2_ibfk_1, record: '3')  |
| test.t2 | check | error    | Corrupt                                                                                          |
+---------+-------+----------+--------------------------------------------------------------------------------------------------+

This page is licensed: GPLv2, originally from fill_help_tables.sql

CHECK VIEW

Syntax

CHECK VIEW view_name

Description

The CHECK VIEW statement checks whether the view algorithm is correct. It is run as part of mariadb-upgrade, and should not normally be required in regular use.

See Also

  • REPAIR VIEW

This page is licensed: CC BY-SA / Gnu FDL

CHECKSUM TABLE

Syntax

CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]

Description

CHECKSUM TABLE reports a table checksum. This is very useful if you want to know if two tables are the same (for example on a master and a replica).

With QUICK, the live table checksum is reported if it is available, or NULL otherwise. This is very fast. A live checksum is enabled by specifying the CHECKSUM=1 table option when you create the table; currently, this is supported only for Aria and MyISAM tables.

With EXTENDED, the entire table is read row by row and the checksum is calculated. This can be very slow for large tables.

If neither QUICK nor EXTENDED is specified, MariaDB returns a live checksum if the table storage engine supports it and scans the table otherwise.

CHECKSUM TABLE requires the SELECT privilege for the table.

For a nonexistent table, CHECKSUM TABLE returnsNULL and generates a warning.

The table row format affects the checksum value. If the row format changes, the checksum will change. This means that when a table created with a MariaDB/MySQL version is upgraded to another version, the checksum value will probably change.

Two identical tables should always match to the same checksum value; however, also for non-identical tables there is a very slight chance that they will return the same value as the hashing algorithm is not completely collision-free.

Identical Tables

Identical tables mean that the CREATE statement is identical and that the following variable, which affects the storage formats, was the same when the tables were created:

  • mysql56-temporal-format

Differences Between MariaDB and MySQL

CHECKSUM TABLE may give a different result as MariaDB doesn't ignore NULLs in the columns like MySQL 5.1 does (later MySQL versions should calculate checksums the same way as MariaDB).

You can get the 'old style' checksum in MariaDB by setting old_mode to COMPAT_5_1_CHECKSUM. Note, however, that the MyISAM and Aria storage engines in MariaDB are using the new checksum internally, so if you are using this old mode, the CHECKSUM command will be slower as it needs to calculate the checksum row by row.

You can get the 'old style' checksum in MariaDB by starting mariadbd with the--old option. Note, however, that the MyISAM and Aria storage engines in MariaDB are using the new checksum internally, so if you are using this old mode, the CHECKSUM command will be slower as it needs to calculate the checksum row by row.

This page is licensed: GPLv2, originally from fill_help_tables.sql

REPAIR TABLE

Syntax

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM] [FORCE
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

Description

REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as

myisamchk --recover tbl_name

or

aria_chk --recover tbl_name

See aria_chk and myisamchk for more.

REPAIR TABLE works for Archive, Aria, CSV, and MyISAM tables. For InnoDB, see recovery modes. For CSV, see also Checking and Repairing CSV Tables. For Archive, this statement also improves compression. If the storage engine does not support this statement, a warning is issued.

This statement requires SELECT and INSERT privileges for the table.

By default, REPAIR TABLE statements are written to the binary log and will be replicated. The NO_WRITE_TO_BINLOG keyword (LOCAL is an alias) will ensure the statement is not written to the binary log.

REPAIR TABLE statements are not logged to the binary log if read_only is set. See also Read-Only Replicas.

REPAIR TABLE statements are logged to the binary log.

When an index is recreated, the storage engine may use a configurable buffer in the process. Incrementing the buffer speeds up the index creation. Aria and MyISAM allocate a buffer whose size is defined by aria_sort_buffer_size or myisam_sort_buffer_size, also used for ALTER TABLE.

QUICK

When specified, REPAIR TABLE will not modify the data file, only attempting to repair the index file. The same behavior can be achieved with myisamchk --recover --quick.

EXTENDED

Creates the index row by row rather than sorting and creating a single index. Similar to myisamchk --safe-recover.

USE_FRM

For use only when the index file is missing or its header corrupted. MariaDB then attempts to recreate it using the .frm file. There is no equivalent myisamchk option.

FORCE

The FORCE argument allows to first run internal repair to fix damaged blocks, and then follow it up with ALTER TABLE (MDEV-33449).

The FORCE option is not available.

Partitioned Tables

REPAIR TABLE is also supported for partitioned tables with the ALTER TABLE ... REPAIR PARTITION statement. However, the USE_FRM option cannot be used with this statement on a partitioned table. See Repairing Partitions for details.

Progress Reporting

The Aria storage engine supports progress reporting for this statement.

See Also

  • mariadb-check

  • aria_chk

  • myisamchk

This page is licensed: GPLv2, originally from fill_help_tables.sql

REPAIR VIEW

Syntax

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] VIEW  view_name[, view_name] ... [FROM MYSQL]

Description

The REPAIR VIEW statement checks whether the view algorithm is correct. It is run as part of mariadb-upgrade, and should not normally be required in regular use.

By default it corrects the checksum and if necessary adds the mariadb-version field. If the optional FROM MYSQL clause is used, and no mariadb-version field is present, the MERGE and TEMPTABLE algorithms are toggled.

By default, REPAIR VIEW statements are written to the binary log and will be replicated. The NO_WRITE_TO_BINLOG keyword (LOCAL is an alias) will ensure the statement is not written to the binary log.

See Also

  • CHECK VIEW

This page is licensed: CC BY-SA / Gnu FDL

TRUNCATE TABLE

Syntax

TRUNCATE [TABLE] tbl_name
  [WAIT n | NOWAIT]

Description

TRUNCATE TABLE empties a table completely. It requires the DROP privilege. See GRANT.

tbl_name can also be specified in the form db_name.tbl_name (see Identifier Qualifiers).

Logically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

TRUNCATE TABLE will fail for an InnoDB table if any FOREIGN KEY constraints from other tables reference the table, returning the error:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

Foreign Key constraints between columns in the same table are permitted.

For an InnoDB table, if there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one. The AUTO_INCREMENT counter is reset by TRUNCATE TABLE, regardless of whether there is a FOREIGN KEY constraint.

The count of rows affected by TRUNCATE TABLE is accurate only when it is mapped to a DELETE statement.

For other storage engines, TRUNCATE TABLE differs fromDELETE in the following ways:

  • Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

  • Truncate operations cause an implicit commit.

  • Truncation operations cannot be performed if the session holds an active table lock.

  • Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is "0 rows affected," which should be interpreted as "no information."

  • As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted.

  • The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

  • When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected.

  • Since truncation of a table does not make any use of DELETE, the TRUNCATE statement does not invoke ON DELETE triggers.

  • TRUNCATE TABLE will only reset the values in the Performance Schema summary tables to zero or null, and will not remove the rows.

For the purposes of binary logging and replication, TRUNCATE TABLE is treated as DROP TABLE followed by CREATE TABLE (DDL rather than DML).

TRUNCATE TABLE does not work on views. Currently, TRUNCATE TABLE drops all historical records from a system-versioned table.

WAIT/NOWAIT

Set the lock wait timeout. See WAIT and NOWAIT.

Oracle-mode

Oracle-mode permits the optional keywords REUSE STORAGE or DROP STORAGE to be used.

TRUNCATE [TABLE] tbl_name [{DROP | REUSE} STORAGE] [WAIT n | NOWAIT]

These have no effect on the operation.

Performance

TRUNCATE TABLE is faster than DELETE, because it drops and re-creates a table.

With InnoDB, TRUNCATE TABLE is slower if innodb_file_per_table=ON is set (the default). This is because TRUNCATE TABLE unlinks the underlying tablespace file, which can be an expensive operation. See MDEV-8069 for more details.

The performance issues with innodb_file_per_table=ON can be exacerbated in cases where the InnoDB buffer pool is very large and innodb_adaptive_hash_index=ON is set. In that case, using DROP TABLE followed by CREATE TABLE instead of TRUNCATE TABLE may perform better. Setting innodb_adaptive_hash_index=OFF can also help.

Setting innodb_adaptive_hash_index=OFF can also improve TRUNCATE TABLE performance in general. See MDEV-16796 for more details.

See Also

  • TRUNCATE function

  • innodb_safe_truncate system variable

  • Oracle mode

This page is licensed: GPLv2, originally from fill_help_tables.sql

Obsolete Table Statements

Table commands that were removed from MariaDB.

BACKUP TABLE (removed)

BACKUP TABLE was removed and is no longer a part of MariaDB

Syntax

BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'

Description

Note: Like RESTORE TABLE, this command was not reliable and has been removed in current versions of MariaDB.

For doing a backup of MariaDB use mysqldump or MariaDB Backup. See Backing Up and Restoring.

This page is licensed: CC BY-SA / Gnu FDL

RESTORE TABLE (removed)

RESTORE TABLE was removed and is no longer a part of MariaDB.

Syntax

RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'

Description

Note:

Like BACKUP TABLE, this command was not reliable and has been removed from MariaDB. For doing a backup of MariaDB use mysqldump, mysqlhotcopy or XtraBackup. See Backing Up and Restoring.

RESTORE TABLE restores the table or tables from a backup that was made with BACKUP TABLE. The directory should be specified as a full path name.

Existing tables are not overwritten; if you try to restore over an existing table, an error occurs. Just as for BACKUP TABLE,RESTORE TABLE works only for MyISAM tables. Restored tables are not replicated from master to slave.

The backup for each table consists of its .frm format file and .MYD data file. The restore operation restores those files, and then uses them to rebuild the .MYI index file. Restoring takes longer than backing up due to the need to rebuild the indexes. The more indexes the table has, the longer it takes.

This page is licensed: GPLv2, originally from fill_help_tables.sql