Search Results
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 ...
https://dev.mysql.com/doc/internals/en/optimizer-order-by-clauses.html
In general, the optimizer will skip the sort procedure for the ORDER BY clause if it sees that the rows will be in order anyway. For the query: SELECT column1 FROM Table1 ORDER BY 'x'; the optimizer will throw out the ORDER BY clause. For the ...
https://dev.mysql.com/doc/refman/8.4/en/explain-output.html
For MyISAM tables, running ANALYZE TABLE helps the optimizer choose better indexes. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. This join type optimization is used most ... The EXPLAIN statement provides information about how MySQL executes ...
https://dev.mysql.com/doc/internals/en/optimizer-index-merge-join-type.html
The Index Merge optimizer collects a list of possible ways to access rows with Index Merge. 7.2.2.5.3 The range Optimizer For range queries, the MySQL optimizer builds a SEL_TREE object which represents a condition in this form: range_cond = ...
https://dev.mysql.com/doc/internals/en/optimizer-and-relations.html
An ANDed search has the form condition1 AND condition2, as in this example: WHERE column1 = 'x' AND column2 = 'y' Here, the optimizer's decision process can be described as follows: If (neither condition is indexed) use sequential scan. The ...
https://dev.mysql.com/doc/internals/en/optimizer-range-join-type.html
The optimizer will use an index (range search) for column1 LIKE 'x%' but not for column1 LIKE '%x' That is, there is no range search if the first character in the pattern is a wildcard. To the optimizer, column1 BETWEEN 5 AND 7 is the same as this ... Some conditions can work with indexes, but over a (possibly wide) range of ...