kazeburoさん のツイートを見てふとやってみたくなった。
反省はしていない。
取りあえずmroonga_snippetで試してみようと思って、
mroonga 2.07のリリースノート をまるっとテストケースにする。
mysql56> CREATE TABLE snippet_test (id int NOT NULL, text text, PRIMARY KEY(id), FULLTEXT KEY(text)) Engine= mroonga;
Query OK, 0 rows affected (0.10 sec)
mysql56> INSERT INTO snippet_test (id, text) VALUES (1, 'An open-source fulltext search engine and column store.');
Query OK, 1 row affected (0.01 sec)
mysql56> INSERT INTO snippet_test (id, text) VALUES (2, 'An open-source storage engine for fast fulltext search with MySQL.');
Query OK, 1 row affected (0.01 sec)
mysql56> INSERT INTO snippet_test (id, text) VALUES (3, 'Tritonn is a patched version of MySQL that supports better fulltext search function with Senna.');
Query OK, 1 row affected (0.00 sec)
mysql56> SELECT @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| DEMAND |
+--------------------+
1 row in set (0.00 sec)
mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0 |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql56> SELECT * FROM snippet_test;
+----+-------------------------------------------------------------------------------------------------+
| id | text |
+----+-------------------------------------------------------------------------------------------------+
| 1 | An open-source fulltext search engine and column store. |
| 2 | An open-source storage engine for fast fulltext search with MySQL. |
| 3 | Tritonn is a patched version of MySQL that supports better fulltext search function with Senna. |
+----+-------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0 |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql56> SELECT sql_cache * FROM snippet_test;
+----+-------------------------------------------------------------------------------------------------+
| id | text |
+----+-------------------------------------------------------------------------------------------------+
| 1 | An open-source fulltext search engine and column store. |
| 2 | An open-source storage engine for fast fulltext search with MySQL. |
| 3 | Tritonn is a patched version of MySQL that supports better fulltext search function with Senna. |
+----+-------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)
mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1 |
+-------------------------+-------+
1 row in set (0.00 sec)
query_cache_type= 2なので、
sql_cacheオプションをつけたときだけクエリーキャッシュに入る。
mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1 |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql56> SELECT sql_cache id, mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '...
',
-> 'fulltext', '', '',
-> 'MySQL', '', '',
-> 'search', '', '')
-> FROM snippet_test WHERE MATCH(text) AGAINST('fulltext');
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '...
',
'fulltext', '', '',
'MySQL', '', '',
|
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | ...fulltext...
... search ...
|
| 2 | ...fulltext...
... search ...
|
| 3 | ...f MySQL ...
...fulltext...
|
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1 |
+-------------------------+-------+
1 row in set (0.00 sec)
おー、ホントだ、載らない。
昔取った杵柄 で、何とかすれば何とでもなりそうな気配がしたので、キューピー3分ハッキングといきます。
まずはsql/sql_cache.cc の中のどこかで、UDFを使ったクエリーはキャッシュしない、と判定されていいるはずなのでそこを探す。たぶん、is_cacheable とかいう名前の何かがあったはず。
mysql-5.6.16/sql/sql_cache.cc
..
3707 TABLE_COUNTER_TYPE
3708 Query_cache::is_cacheable(THD *thd, size_t query_len, const char *query,
3709 LEX *lex,
3710 TABLE_LIST *tables_used, uint8 *tables_type)
3711 {
3712 TABLE_COUNTER_TYPE table_count;
3713 DBUG_ENTER("Query_cache::is_cacheable");
3714
3715 if (query_cache_is_cacheable_query(lex) &&
3716 (thd->variables.query_cache_type == 1 ||
3717 (thd->variables.query_cache_type == 2 && (lex->select_lex.options &
3718 OPTION_TO_QUERY_CACHE))))
3719 {
..
ここかなー。
thd->variables.query_cache_typeはSET SESSION query_ache_type= ..で指定するやつだし、lex->select_lex.optionsは"sql_cache", "sql_no_cache"とかを持つところだから違うし、明らかにquery_cache_is_cacheable_queryが怪しい。
mysql-5.6.16/sql/sql_cache.h
..
562 #define query_cache_is_cacheable_query(L) \
563 (((L)->sql_command == SQLCOM_SELECT) && (L)->safe_to_cache_query && \
564 !(L)->describe)
..
lex->safe_to_cache_queryかな？
mysql-5.6.16/sql/item_create.cc
..
2733 Item*
2734 Create_udf_func::create(THD *thd, udf_func *udf, List<Item> *item_list)
2735 {
..
2826 thd->lex->safe_to_cache_query= 0;
2827 DBUG_RETURN(func);
2828 }
..
ここっぽいので取り敢えず= 1に書き換えてmakeする。
mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0 |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql56> SELECT sql_cache id, mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '...
',
-> 'fulltext', '', '',
-> 'MySQL', '', '',
-> 'search', '', '')
-> FROM snippet_test WHERE MATCH(text) AGAINST('fulltext');
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | mroonga_snippet(text, 8, 2, 'utf8_general_ci', 1, 1, '...', '...
',
'fulltext', '', '',
'MySQL', '', '',
|
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | ...fulltext...
... search ...
|
| 2 | ...fulltext...
... search ...
|
| 3 | ...f MySQL ...
...fulltext...
|
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql56> SHOW GLOBAL STATUS LIKE 'Qcache_queries_in_cache';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1 |
+-------------------------+-------+
1 row in set (0.00 sec)
載った。満足。