Search Results
https://dev.mysql.com/doc/refman/8.4/en/limit-optimization.html
MySQL sometimes optimizes a query that has a LIMIT row_count clause and no HAVING clause: If you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan. If an index is not used for ... If you need only a specified number of rows from a result set, use a LIMIT clause in the query, rather than fetching the whole result set and throwing away the extra ...
https://dev.mysql.com/doc/refman/8.4/en/row-constructor-optimization.html
For example, these two statements are semantically equivalent: SELECT * FROM t1 WHERE (column1,column2) = (1,1); SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1; In addition, the optimizer handles both expressions the same way. The optimizer is ...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 ...
https://dev.mysql.com/doc/heatwave/en/mys-hw-workload-optimization-olap.html
Workload optimization for online analytical processing, OLAP, includes using dictionary encoding for certain string columns and defining data placement keys to optimize for JOIN and GROUP BY query performance. Apply these optimizations manually, or ...Auto Query Time Estimation provides runtime information for successfully executed queries and runtime estimates for EXPLAIN queries, queries cancelled with Ctrl+C, and queries that fail due to out of memory ...
https://dev.mysql.com/doc/refman/8.4/en/controlling-optimizer.html
MySQL provides optimizer control through system variables that affect how query plans are evaluated, switchable optimizations, optimizer and index hints, and the optimizer cost model. The server maintains histogram statistics about column values in ...Like other data dictionary tables, this table is not directly accessible by ...
https://dev.mysql.com/doc/refman/8.4/en/engine-condition-pushdown-optimization.html
This optimization improves the efficiency of direct comparisons between a nonindexed column and a constant. This optimization can be used only by the NDB storage engine. To disable it at server startup, set the optimizer_switch system variable's ...
https://dev.mysql.com/doc/refman/8.4/en/index-condition-pushdown-optimization.html
Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Applicability of the Index Condition Pushdown optimization is subject to these conditions: ICP is used for the range, ref, ...
https://dev.mysql.com/doc/refman/8.4/en/performance-schema-optimization.html
Performance Schema indexes give the optimizer access to execution plans other than full table scans. row *************************** id: 1 select_type: SIMPLE table: accounts partitions: NULL type: const possible_keys: ACCOUNT key: ACCOUNT key_len: ... Applications that monitor databases may make frequent use of Performance Schema ...
https://dev.mysql.com/doc/refman/8.4/en/spatial-index-optimization.html
The optimizer checks the SRID attribute for indexed columns to determine which spatial reference system (SRS) to use for comparisons, and uses calculations appropriate to the SRS. (Prior to MySQL 8.4, the optimizer performs comparisons of SPATIAL ...
https://dev.mysql.com/doc/refman/8.4/en/constant-folding-optimization.html
Comparisons between constants and column values in which the constant value is out of range or of the wrong type with respect to the column type are now handled once during query optimization rather row-by-row than during execution. This makes it ...The comparisons that can be treated in this manner are >, >=, <, <=, <>/!=, =, and ...
https://dev.mysql.com/doc/refman/8.4/en/nested-join-optimization.html
Thus, the optimization of pushing conditions out of the inner nested loops cannot be applied directly to queries with outer joins. The following discussion refers to the join syntax described in Section 15.2.13.2, “JOIN Clause”. The syntax of ...