Description:
The use of the new optimizer hints are not reflected in the computation of query digest. Hence, queries with and without hints will get the same digest. This is not good since performance schema should aggregate statistics from queries with different hints separately. Otherwise it will not be possible to see what effects the hints have on performance. Note also that digest will reflect when legacy hint syntax is used.
Example:
mysql> select n_name from nation where n_regionkey = 4;
+--------------+
| n_name |
+--------------+
| EGYPT |
| IRAN |
| IRAQ |
| JORDAN |
| SAUDI ARABIA |
+--------------+
5 rows in set (0.00 sec)
mysql> select /*+ NO_RANGE_OPTIMIZATION(nation) */ n_name from nation where n_regionkey = 4;
+--------------+
| n_name |
+--------------+
| EGYPT |
| IRAN |
| IRAQ |
| JORDAN |
| SAUDI ARABIA |
+--------------+
5 rows in set (0.00 sec)
mysql> select digest,digest_text from performance_schema.events_statements_history order by timer_start desc limit 2;
+----------------------------------+--------------------------------------------------------+
| digest | digest_text |
+----------------------------------+--------------------------------------------------------+
| 535eb7be2a896c37473991d15102d20e | SELECT `n_name` FROM `nation` WHERE `n_regionkey` = ? |
| 535eb7be2a896c37473991d15102d20e | SELECT `n_name` FROM `nation` WHERE `n_regionkey` = ? |
+----------------------------------+--------------------------------------------------------+
2 rows in set (0.00 sec)
How to repeat:
CREATE TABLE t1(i INTEGER, j INTEGER, key k(i));
INSERT INTO t1 VALUES (1,2), (3,4);
SELECT j FROM t1 WHERE i > 1;
SELECT /*+ NO_RANGE_OPTIMIZATION(t1) */ j FROM t1 WHERE i > 1;
SELECT digest, digest_text FROM performance_schema.events_statements_history ORDER BY timer_start DESC LIMIT 2;
DROP TABLE t1;
Suggested fix:
Let new optimizer hint be reflected in digest and let the digest text include the hint.
