Search Results
https://dev.mysql.com/doc/refman/8.4/en/optimizer-issues.html
To use it, just add the keyword EXPLAIN to the front of your SELECT statement: mysql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.i = t2.i; EXPLAIN is discussed in more detail in Section 15.8.2, “EXPLAIN Statement”. Use ANALYZE TABLE tbl_name to ...
https://dev.mysql.com/doc/refman/8.4/en/optimizing-innodb-storage-layout.html
In InnoDB, having a long PRIMARY KEY (either a single column with a lengthy value, or several columns that form a long composite value) wastes a lot of disk space. The primary key value for a row is duplicated in all the secondary index records that ... Once your data reaches a stable size, or a growing table has increased by tens or some hundreds of megabytes, consider using the OPTIMIZE TABLE statement to reorganize the table and compact any wasted ...
https://dev.mysql.com/doc/refman/8.4/en/optimizing-myisam-bulk-data-loading.html
Use myisamchk --keys-used=0 -rq /path/to/db/tbl_name to remove all use of indexes for the table. To increase performance for MyISAM tables, for both LOAD DATA and INSERT, enlarge the key cache by increasing the key_buffer_size system variable.
https://dev.mysql.com/doc/refman/8.4/en/partitioning-pruning.html
An INSERT statement also accesses only one partition per inserted row; this is true even for a table that is partitioned by HASH or KEY although this is not currently shown in the output of EXPLAIN. For tables that are partitioned by HASH or ... The ...
https://dev.mysql.com/doc/refman/8.4/en/persisted-system-variables.html
Syntax for Persisting System Variables These SET syntax options are available for persisting system variables: To persist a global system variable to the mysqld-auto.cnf option file in the data directory, precede the variable name by the PERSIST ...
https://dev.mysql.com/doc/refman/8.4/en/replica-logs-status.html
A replica server creates two replication metadata repositories, the connection metadata repository and the applier metadata repository. If binary log file position based replication is in use, when the replica restarts, it reads the two ...
https://dev.mysql.com/doc/refman/8.4/en/replication-privilege-checks.html
The REQUIRE_TABLE_PRIMARY_KEY_CHECK option makes the replication channel use its own policy for primary key checks. Setting ON means that primary keys are always required, and setting OFF means that primary keys are never required. The default ...
https://dev.mysql.com/doc/refman/8.4/en/row-constructor-optimization.html
Consider the following table, which has a primary key on (c1, c2, c3): CREATE TABLE t1 ( c1 INT, c2 INT, c3 INT, c4 CHAR(100), PRIMARY KEY(c1,c2,c3) ); In this query, the WHERE clause uses all columns in the index. However, the row constructor ...
https://dev.mysql.com/doc/refman/8.4/en/storage-requirements.html
InnoDB Table Storage Requirements NDB Table Storage Requirements Numeric Type Storage Requirements Date and Time Type Storage Requirements String Type Storage Requirements Spatial Type Storage Requirements JSON Storage Requirements The storage ...
https://dev.mysql.com/doc/refman/8.4/en/subquery-optimization-with-exists.html
WHERE subquery_where AND (outer_expr=inner_expr OR inner_expr IS NULL)) The need to evaluate the extra IS NULL condition is why MySQL has the ref_or_null access method: mysql> EXPLAIN SELECT outer_expr IN (SELECT t2.maybe_null_key FROM t2, t3 WHERE ... Certain optimizations are applicable to comparisons that use the IN (or =ANY) operator to test subquery ...