UPDATE
Not related to LIMIT clause: LARGE dataset is not being cached!
After hours of testing and observation, keeping the dataset smaller with LIMIT makes the cache to work, but a larger dataset is not being cached. Can you please direct me to some solution with this?
Working query: SELECT option_name, option_value FROM lsk_options WHERE autoload = 'yes' LIMIT 999
Failing query: SELECT option_name, option_value FROM lsk_options WHERE autoload = 'yes' LIMIT 1999
mysql-query_cache_size_MB = 20480
==============================================
ProxySQL is caching the statement with the LIMIT clause just fine, but never caching the one without the LIMIT clause!
Query
SELECT option_name, option_value FROM lsk_options WHERE autoload = 'yes';
SELECT option_name, option_value FROM lsk_options WHERE autoload = 'yes' LIMIT 99;
Rule
+---------+--------------------+--------------------------------------+-----------------------+-----------+
| rule_id | digest | match_pattern | destination_hostgroup | cache_ttl |
+---------+--------------------+--------------------------------------+-----------------------+-----------+
| 31 | NULL | ^SELECT .+ FROM lsk_options WHERE .+ | NULL | 9999999 |
+---------+--------------------+--------------------------------------+-----------------------+-----------+
Status
+------------+----------+-----------+------------------------------------------------------------------------------+
| count_star | sum_time | hostgroup | digest_text |
+------------+----------+-----------+------------------------------------------------------------------------------+
| 1 | 113717 | 2 | SELECT option_name, option_value FROM lsk_options WHERE autoload = ? |
| 1 | 0 | -1 | SELECT option_name, option_value FROM lsk_options WHERE autoload = ? LIMIT ? |
+------------+----------+-----------+------------------------------------------------------------------------------+
Variable
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| mysql-query_cache_size_MB | 20480 |
| mysql-query_cache_stores_empty_result | true |
+---------------------------------------+-------+