The same query with and without a comment results in cache misses

[Djuke]

Hello,

I've just started using ProxySQL and in some of our queries we add a traceparent as a comment. When executing the same query but with a different traceparent the query cache is not being used, rather the count_star of the same query digest in the stats_mysql_query_digest is bumped of hostgroup > -1. This is not specific due to a traceparent that we add, but if the query is the same and the comment is different you'll see the same result in the stats_mysql_query_digest.

Running the exact same query twice will cache the query with the correct mysql query rule

mysql> SELECT * FROM test;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

results in

MySQL [(none)]> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest WHERE digest_text like '%FROM test' ORDER BY sum_time DESC;
+------------+----------+-----------+--------------------+--------------------+
| count_star | sum_time | hostgroup | digest             | digest_text        |
+------------+----------+-----------+--------------------+--------------------+
| 1          | 888      | 10        | 0xf32d2eaed69c6b36 | SELECT * FROM test |
| 1          | 0        | -1        | 0xf32d2eaed69c6b36 | SELECT * FROM test |
+------------+----------+-----------+--------------------+--------------------+
2 rows in set (0.014 sec)

The running the same query with a comment (within the cache time frame)

mysql> SELECT * FROM test/* 1 */;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

result in

MySQL [(none)]> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest WHERE digest_text like '%FROM test' ORDER BY sum_time DESC;
+------------+----------+-----------+--------------------+--------------------+
| count_star | sum_time | hostgroup | digest             | digest_text        |
+------------+----------+-----------+--------------------+--------------------+
| 2          | 1894     | 10        | 0xf32d2eaed69c6b36 | SELECT * FROM test |
| 1          | 0        | -1        | 0xf32d2eaed69c6b36 | SELECT * FROM test |
+------------+----------+-----------+--------------------+--------------------+
2 rows in set (0.024 sec)

I would expect for the last query to also hit the cache because the query digest is the same.

[JavierJF]

Hi @Djuke,

this is expected behavior, same digests doesn't mean same queries. Query cache is only hit when the same exact query that matched the query rule is executed again, each of those queries generate different cache entries. Consider the simple cases of SELECT 1 and SELECT 2, both generates identical digests SELECT ?, yet, you wouldn't want to swap the resultset of one for the other.

Hope this clarifies the doubts about this behavior. Regards, Javier.

[JavierJF]

Hi @Djuke,

I'm closing this issue due to inactivity and because it was expected behavior. Please feel free to comment on it if you still have doubts.

Regards, Javier.