Search Results
https://dev.mysql.com/doc/refman/8.4/en/innodb-performance-midpoint_insertion.html
You can control the insertion point in the LRU list and choose whether InnoDB applies the same optimization to blocks brought into the buffer pool by table or index scans. The optimization that keeps the buffer pool from being churned by read-ahead ... Rather than using a strict LRU algorithm, InnoDB uses a technique to minimize the amount of data that is brought into the buffer pool and never accessed ...
https://dev.mysql.com/doc/refman/8.4/en/internal-temporary-tables.html
Tables created for subquery or semijoin materialization (see Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”). To determine whether a statement requires a temporary table, use EXPLAIN and ...
https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html
Queries using JSON_CONTAINS() on InnoDB tables can be optimized using multi-valued indexes; see Multi-Valued Indexes, for more information. Queries on JSON columns of InnoDB tables using JSON_OVERLAPS() in the WHERE clause can be optimized using ...
https://dev.mysql.com/doc/refman/8.4/en/mathematical-functions.html
Thus, for optimizer purposes, RAND() is not a constant value and cannot be used for index optimizations. ABS(X) Returns the absolute value of X, or NULL if X is NULL. An implication of this is that ABS(-9223372036854775808) produces an error because ...
https://dev.mysql.com/doc/refman/8.4/en/myisam-table-maintenance.html
You can use myisamchk to check, repair, or optimize database tables. This section discusses how to use myisamchk to check or repair MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes). For general myisamchk background, ...
https://dev.mysql.com/doc/refman/8.4/en/partitioning-limitations.html
The statements CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE are supported for partitioned tables. ANALYZE, CHECK, OPTIMIZE, REPAIR, and TRUNCATE operations are supported with subpartitions. This section discusses current ...
https://dev.mysql.com/doc/refman/8.4/en/row-subqueries.html
Scalar or column subqueries return a single value or a column of values. A row subquery is a subquery variant that returns a single row and can thus return more than one column value. If this row has col3 and col4 values equal to the col1 and col2 ...
https://dev.mysql.com/doc/refman/8.4/en/spatial-type-overview.html
However, the optimizer cannot use SPATIAL indexes on them until the column definition is modified to include an SRID attribute, which may require that the column contents first be modified so that all values have the same SRID. MySQL has spatial ...
https://dev.mysql.com/doc/refman/8.4/en/table-scan-avoidance.html
For large tables, try the following techniques to avoid having the optimizer incorrectly choose a table scan: Use ANALYZE TABLE tbl_name to update the key distributions for the scanned table. Start mysqld with the --max-seeks-for-key=1000 option or ... The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a ...
https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-1.html
For a SELECT with ORDER BY and LIMIT, the optimizer first chose a full table scan with a very expensive cost, then performed another check and used the perform_order_index type of path, but this was not reflected by the cost in the optimizer plan.