Bug #79808 | Manual does NOT explain when "Select tables optimized away" is possible (or not) | ||
---|---|---|---|
Submitted: | 29 Dec 2015 17:50 | Modified: | 9 Mar 2016 15:20 |
Reporter: | Valeriy Kravchuk | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | any | OS: | Any |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
Tags: | compound, MAX, min, missing manual, Select tables optimized away |
[29 Dec 2015 17:50]
Valeriy Kravchuk
[29 Dec 2015 17:57]
Valeriy Kravchuk
Added "missing manual" tag...
[29 Dec 2015 20:57]
MySQL Verification Team
Thank you for the bug report.
[9 Mar 2016 15:20]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly. Revised text: The optimizer determined 1) that at most one row should be returned, and 2) that to produce this row, a deterministic set of rows must be read. When the rows to be read can be read during the optimization phase (for example, by reading index rows), there is no need to read any tables during query execution. The first condition is fulfilled when the query is implicitly grouped (contains an aggregate function but no GROUP BY clause). The second condition is fulfilled when one row lookup is performed per index used. The number of indexes read determines the number of rows to read. Consider the following implicitly grouped query: SELECT MIN(c1), MIN(c2) FROM t1; Suppose that MIN(c1) can be retrieved by reading one index row and MIN(c2) can be retrieved by reading one row from a different index. That is, for each column c1 and c2, there exists an index where the column is the first column of the index. In this case, one row is returned, produced by reading two deterministic rows. This Extra value does not occur if the rows to read are not deterministic. Consider this query: SELECT MIN(c2) FROM t1 WHERE c1 <= 10; Suppose that (c1, c2) is a covering index. Using this index, all rows with c1 <= 10 must be scanned to find the minimum c2 value. By contrast, consider this query: SELECT MIN(c2) FROM t1 WHERE c1 = 10; In this case, the first index row with c1 = 10 contains the minimum c2 value. Only one row must be read to produce the returned row. For storage engines that maintain an exact row count per table (such as MyISAM, but not InnoDB), this Extra value can occur for COUNT(*) queries for which the WHERE clause is missing or always true and there is no GROUP BY clause. (This is an instance of an implicitly grouped query where the storage engine influences whether a deterministic number of rows can be read.)