Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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 and store key distribution. This statement updates index statistics used by the optimizer to choose the best execution plan.
ANALYZE TABLE analyzes and stores the key distribution for a table (). This statement works with , , and tables. During the analysis, InnoDB will allow reads/writes, and MyISAM/Aria reads/inserts. For MyISAM tables, this statement is equivalent to using .
ANALYZE uses histograms, which can provide a better selectivity than InnoDB statistics offer. InnoDB statistics work with a limited sample set and are therefore not as accurate as persistent statistics can be. For more information on how the analysis works within InnoDB, see .
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 for the table.
By default, ANALYZE TABLE statements are written to the and will be . 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 is set. See also .
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 ... ANALYZE PARTITION to analyze one or more partitions.
The storage engine supports for the ANALYZE TABLE statement.
When using ANALYZE TABLE PERSISTENT, MariaDB skips long / columns during statistics collection if they exceed the value of the system variable.
This prevents excessive disk usage when analyzing tables with large text columns.
If a column is longer than analyze_max_length
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 or 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.
ANALYZE TABLE supports (EITS). See for more information.
You can run the statement on all columns with this statement — however, be aware that this can take a long time for very large (500+ GB) tables:
Focusing on particular columns, the statement looks like this — it collects statistics only for the specified table columns:
Focusing on columns helps, among other things, avoid including BLOB columns, for which MariaDB doesn't collect statistics.
Queries that benefit most are those where the query plan depends on the knowing condition selectivities, the most important ones being JOIN queries and those with an ORDER BY ... LIMIT clause. To benefit, the query must have a condition that may or may not be selective, for example:
Here, the optimizer benefits from knowing these statistics:
Which fraction of customers are in the EMEA region?
Which fraction of orders are URGENT?
For that situation, you can issue this statement:
EITS (engine-independent table statistics) provide way more data than InnoDB-internal statistics. The downside is that EITS are never automatically updated, and it takes time to collect them.
InnoDB statistics, on the other hand, provide less data, but they are automatically updated.
Engine-independent statistics can be controlled (enabled and disabled) using the and the . InnoDB-persistent statistics are controlled with the (allowing you to turn InnoDB statistics on or off). 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.
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.
(mariadb.org blog)
This page is licensed: GPLv2, originally from
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [,tbl_name ...]
[PERSISTENT FOR
{ ALL
| COLUMNS ([col_name [,col_name ...]]) INDEXES ([index_name [,index_name ...]])
}
]If a long column is explicitly specified in FOR COLUMNS(), it is still analyzed, regardless of its size.
Example:
The functionality for skipping long CHAR and VARCHAR columns isn't available.
when the distribution of data in a table has changed significantly, for example, if you have loaded a batch of data or deleted a large amount of data.
11.0.1
Log slow OPTIMIZE, ANALYZE, ALTER, and other administrative statements to the slow log if it is open. Deprecated. Use instead.
(all versions)
Having an admin in the log_slow_filter will add slow ANALYZE_TABLE statements to the slow log.
(all versions)
For calculating the number of duplicates, ANALYZE TABLE uses a buffer of bytes per column. You can slightly increase the speed of ANALYZE TABLE by increasing this variable.
MariaDB 10.6.23 / 10.11.14 / 11.4.8 / 11.8.3 / 12.0.2 / 12.1.1
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.
10.4.3-11.0
Specifies the type of histograms created by ANALYZE. Options are #SINGLE_PREC_HB,DOUBLE_PREC_HB or JSON_HB.
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.]]
ANALYZE TABLE tbl PERSISTENT FOR ALLANALYZE TABLE tbl PERSISTENT FOR COLUMS (column1, column2, ...) INDEXES (index1, ...)SELECT *
FROM orders, customer
WHERE
orders.o_custkey = customer.c_custkey -- join condition
AND customer.c_area = 'EMEA' -- (1)
AND orders.o_priority= 'URGENT' -- (2)ANALYZE TABLE orders, customer PERSISTENT FOR ALL-- 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;SET GLOBAL analyze_max_length = 50000;
ANALYZE TABLE large_text_table PERSISTENT;ANALYZE TABLE large_text_table PERSISTENT FOR COLUMNS(long_description);CREATE TABLE product_data (
id INT PRIMARY KEY,
name VARCHAR(100),
description VARCHAR(50000), -- long column
specs VARCHAR(1000)
);
-- Set limit
SET SESSION analyze_max_length = 10000;
-- Run analysis without explicitly selecting columns
ANALYZE TABLE product_data PERSISTENT;
-- 'description' will be skipped due to length > 10000
-- To include it anyway
ANALYZE TABLE product_data PERSISTENT FOR COLUMNS(description);Browse documentation for removed table commands. This section covers obsolete statements like BACKUP TABLE and RESTORE TABLE.
Verify the validity of a view's algorithm. This statement checks if the view definition is correct and references existing tables.
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
BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'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 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:
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
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.
This page is licensed: CC BY-SA / Gnu FDL
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] VIEW view_name[, view_name] ... [FROM MYSQL]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
CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'Repair corrupted tables. This statement fixes errors in tables for supported storage engines like MyISAM, Aria, and Archive.
REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM] [FORCEREPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
[QUICK] [EXTENDED] [USE_FRM]REPAIR TABLE repairs a possibly corrupted table. By default, it has the same effect as
or
See and for more.
REPAIR TABLE works for , , , and tables. For , see . For CSV, see also . For Archive, this statement also improves compression. If the storage engine does not support this statement, a warning is issued.
This statement requires for the table.
By default, REPAIR TABLE statements are written to the and will be . 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 is set. See also .
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. and allocate a buffer whose size is defined by or , also used for .
When specified, REPAIR TABLE will not modify the data file, only attempting to repair the index file. The same behavior can be achieved with .
Creates the index row by row rather than sorting and creating a single index. Similar to .
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 option.
The FORCE argument allows to first run internal repair to fix damaged blocks and then follow it up with ALTER TABLE ().
The FORCE option is not available.
REPAIR TABLE is also supported for with the statement. However, the USE_FRM option cannot be used with this statement on a partitioned table. See for details.
The storage engine supports for this statement.
This page is licensed: GPLv2, originally from
myisamchk --recover tbl_namearia_chk --recover tbl_nameEmpty a table completely. This DDL statement drops and re-creates the table, offering a faster alternative to DELETE for removing all rows.
TRUNCATE TABLE empties a table completely. It requires the DROP privilege. See .
tbl_name can also be specified in the form db_name.tbl_name (see ).
Logically, TRUNCATE TABLE is equivalent to a statement that deletes all rows, but there are practical differences under some circumstances.
TRUNCATE TABLE will fail for an if any FOREIGN KEY constraints from other tables reference the table, returning the error:
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 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."
For the purposes of binary logging and , TRUNCATE TABLE is treated as followed by (DDL rather than DML).
TRUNCATE TABLE does not work on . Currently, TRUNCATE TABLE drops all historical records from a .
Set the lock wait timeout. See .
permits the optional keywords REUSE STORAGE or DROP STORAGE to be used.
These have no effect on the operation.
TRUNCATE TABLE is faster than , because it drops and re-creates a table.
With , TRUNCATE TABLE is slower if is set (the default). This is because TRUNCATE TABLE unlinks the underlying tablespace file, which can be an expensive operation. See for more details.
The performance issues with can be exacerbated in cases where the is very large and is set. In that case, using followed by instead of TRUNCATE TABLE may perform better. Setting can also help.
Setting can also improve TRUNCATE TABLE performance in general. See for more details.
system variable
This page is licensed: GPLv2, originally from
Check tables or views for errors. This statement verifies the integrity of table structure and data for supported storage engines.
CHECK TABLE checks a table or tables for errors. CHECK TABLE works for , , , , , and, from , tables. For Aria and MyISAM tables, the key statistics are updated as well. For CSV, see also .
As an alternative, is a command-line tool for checking MyISAM tables when the tables are not being accessed. For Aria tables, there is a similar tool: .
For checking ' integrity, 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 to check one or more partitions.
The meaning of the different options is as follows - note that this can vary a bit between storage engines:
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 storage engine supports for this statement.
If you want to know if two tables are identical, take a look at .
If CHECK TABLE finds an error in an InnoDB table, MariaDB might shut down 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.
In newer MariaDB versions, the EXTENDED clause provides more information:
This page is licensed: GPLv2, originally from
TRUNCATE [TABLE] tbl_name
[WAIT n | NOWAIT]CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}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 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 have changed since the 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 the 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 exist, and for those index keys, they point back to the primary clustered key. This may take a long time on large tables.
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraintTRUNCATE [TABLE] tbl_name [{DROP | REUSE} STORAGE] [WAIT n | NOWAIT]CHECK TABLE y EXTENDED;
+--------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.y | check | status | OK |
+--------+-------+----------+----------+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 |
+---------+-------+----------+--------------------------------------------------------------------------------------------------+