8.2.1.13 Constant-Folding Optimization

Inhoudsopgave

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. The comparisons that can be treated in this manner are >, >=, <, <=, <>/!=, =, and <=>.

Consider the table created by the following statement:

CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);

The WHERE condition in the query SELECT * FROM t WHERE c < 256 contains the integral constant 256 which is out of range for a TINYINT UNSIGNED column. Previously, this was handled by treating both operands as the larger type, but now, since any allowed value for c is less than the constant, the WHERE expression can instead be folded as WHERE 1, so that the query is rewritten as SELECT * FROM t WHERE 1.

This makes it possible for the optimizer to remove the WHERE expression altogether. If the column c were nullable (that is, defined only as TINYINT UNSIGNED) the query would be rewritten like this:

SELECT * FROM t WHERE ti IS NOT NULL

Folding is performed for constants compared to supported MySQL column types as follows:

Integer column type. Integer types are compared with constants of the following types as described here:
- Integer value. If the constant is out of range for the column type, the comparison is folded to 1 or IS NOT NULL, as already shown.
  If the constant is a range boundary, the comparison is folded to =. For example (using the same table as already defined):
  1. mysql> EXPLAIN SELECT * FROM t WHERE c >= 255;
  2. *************************** 1. row ***************************
  3. id: 1
  4. select_type: SIMPLE
  5. table: t
  6. partitions: NULL
  7. type: ALL
  8. possible_keys: NULL
  9. key: NULL
  10. key_len: NULL
  11. ref: NULL
  12. rows: 5
  13. filtered: 20.00
  14. Extra: Using where
  15. 1 row in set, 1 warning (0.00 sec)
  17. mysql> SHOW WARNINGS;
  18. *************************** 1. row ***************************
  19. Level: Note
  20. Code: 1003
  21. Message: /* select#1 */ select `test`.`t`.`ti` AS `ti` from `test`.`t` where (`test`.`t`.`ti` = 255)
  22. 1 row in set (0.00 sec)
- Floating- or fixed-point value. If the constant is one of the decimal types (such as DECIMAL, REAL, DOUBLE, or FLOAT) and has a nonzero decimal portion, it cannot be equal; fold accordingly. For other comparisons, round up or down to an integer value according to the sign, then perform a range check and handle as already described for integer-integer comparisons.
  A REAL value that is too small to be represented as DECIMAL is rounded to .01 or -.01 depending on the sign, then handled as a DECIMAL.
- String types. Try to interpret the string value as an integer type, then handle the comparison as between integer values. If this fails, attempt to handle the value as a REAL.
DECIMAL or REAL column. Decimal types are compared with constants of the following types as described here:
- Integer value. Perform a range check against the column value's integer part. If no folding results, convert the constant to DECIMAL with the same number of decimal places as the column value, then check it as a DECIMAL (see next).
- DECIMAL or REAL value. Check for overflow (that is, whether the constant has more digits in its integer part than allowed for the column's decimal type). If so, fold.
  If the constant has more significant fractional digits than column's type, truncate the constant. If the comparison operator is = or <>, fold. If the operator is >= or <=, adjust the operator due to truncation. For example, if column's type is DECIMAL(3,1), SELECT * FROM t WHERE f >= 10.13 becomes SELECT * FROM t WHERE f > 10.1.
  If the constant has fewer decimal digits than the column's type, convert it to a constant with same number of digits. For underflow of a REAL value (that is, too few fractional digits to represent it), convert the constant to decimal 0.
- String value. If the value can be interpreted as an integer type, handle it as such. Otherwise, try to handle it as REAL.
FLOAT or DOUBLE column. FLOAT(m,n) or DOUBLE(m,n) values compared with constants are handled as follows:
If the value overflows the range of the column, fold.
If the value has more than n decimals, truncate, compensating during folding. For = and <> comparisons, fold to TRUE, FALSE, or IS [NOT] NULL as described previously; for other operators, adjust the operator.
If the value has more than m integer digits, fold.

Limitations. This optimization cannot be used in the following cases:

With comparisons using BETWEEN or IN.
With BIT columns or columns using date or time types.
During the preparation phase for a prepared statement, although it can be applied during the optimization phase when the prepared statement is actually executed. This due to the fact that, during statement preparation, the value of the constant is not yet known.

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-constant-folding-optimization.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut