Search Results
https://dev.mysql.com/doc/internals/en/optimizer-joins-access-methods.html
Bad join choices can cause more damage than bad choices in single-table searches, so MySQL developers have spent proportionally more time making sure that the tables in a query are joined in an optimal order and that optimal access methods (often ...A combination of a fixed order in which tables are joined and the corresponding table access methods for each table is called query execution plan ...
https://dev.mysql.com/doc/refman/8.4/en/innodb-persistent-stats.html
The persistent optimizer statistics feature improves plan stability by storing statistics to disk and making them persistent across server restarts so that the optimizer is more likely to make consistent choices each time for a given query.
https://dev.mysql.com/doc/refman/8.4/en/glossary.html
The adaptive flushing algorithm extends this process by estimating the optimal rate to perform these periodic flushes, based on the rate of flushing and how fast redo information is generated. adaptive hash index An optimization for InnoDB tables ...
https://dev.mysql.com/doc/refman/8.4/en/cost-model.html
To generate execution plans, the optimizer uses a cost model that is based on estimates of the cost of various operations that occur during query execution. The optimizer has a set of compiled-in default “cost constants” available to it to make ...These estimates are stored in the server_cost and engine_cost tables in the mysql system database and are configurable at any ...
https://dev.mysql.com/doc/refman/8.4/en/innodb-statistics-estimation.html
Optimizer statistics are not persisted to disk when innodb_stats_persistent=OFF or when individual tables are created or altered with STATS_PERSISTENT=0. Optimizer statistics are persisted to disk by default, enabled by the innodb_stats_persistent ...Instead, statistics are stored in memory, and are lost when the server is shut ...
https://dev.mysql.com/doc/refman/8.4/en/semijoins-antijoins.html
The optimizer uses semijoin strategies to improve subquery execution, as described in this section. The same duplicate-free result can be obtained by using a subquery: SELECT class_num, class_name FROM class WHERE class_num IN (SELECT class_num FROM ... A semijoin is a preparation-time transformation that enables multiple execution strategies such as table pullout, duplicate weedout, first match, loose scan, and ...
https://dev.mysql.com/doc/refman/8.4/en/using-explain.html
When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. You can also use EXPLAIN to check whether the optimizer joins the tables in an optimal order. To give a hint to the ... The EXPLAIN statement provides information about how MySQL executes statements: EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE ...
https://dev.mysql.com/doc/internals/en/optimizer-group-by-related-conditions.html
These are the main optimizations that take place for GROUP BY and related items (HAVING, COUNT(), MAX(), MIN(), SUM(), AVG(), DISTINCT()). For the case GROUP BY x ORDER BY x, the optimizer will realize that the ORDER BY is unnecessary, because the ...If the table handler has a quick row-count available, then the query SELECT COUNT(*) FROM Table1; gets the count without going through all the ...
https://dev.mysql.com/doc/refman/8.4/en/subquery-materialization.html
The optimizer uses materialization to enable more efficient subquery processing. The optimizer may index the table with a hash index to make lookups fast and inexpensive. If materialization is not used, the optimizer sometimes rewrites a ...
https://dev.mysql.com/doc/internals/en/optimizer-eliminating-dead-code.html
Informally, we at MySQL say that the WHERE has been “optimized away”. If a column cannot be NULL, the optimizer removes any non-relevant IS NULL conditions. The optimizer leaves IS NULL conditions alone in such exceptional situations. The ... A ...