{"id":4353,"date":"2012-03-06T15:18:36","date_gmt":"2012-03-06T13:18:36","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=4353"},"modified":"2012-03-06T18:39:24","modified_gmt":"2012-03-06T16:39:24","slug":"auto-caching-tables","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/auto-caching-tables","title":{"rendered":"Auto caching tables"},"content":{"rendered":"

Is there a way to create a caching table, some sort of a materialized view, such that upon selecting<\/em> from that table, its data is validated\/invalidated?<\/p>\n

Hint<\/em>: yes.<\/p>\n

But to elaborate the point: say I have some table data_table<\/strong>. Can I rewrite all my queries which access data_table<\/strong> to read from some autocache_data_table<\/strong>, but have nothing changed in the query itself? No caveats, no additional WHERE<\/strong>s, and still have that autocache_data_table<\/strong> provide with the correct data, dynamically updated by some rule of our choice<\/em>?<\/p>\n

And: no crontab<\/em>, no event scheduler<\/em>, and no funny triggers on data_table<\/strong>? In such way that invalidation\/revalidation occurs upon SELECT<\/strong><\/em>?<\/p>\n

Well, yes.<\/p>\n

This post is long, but I suggest you read it through to understand the mechanism, it will be worthwhile.<\/p>\n

Background<\/h4>\n

The following derives from my long research on how to provide better, faster and safer<\/em> access to INFORMATION_SCHEMA<\/strong> tables. It is however not limited to this exact scenario, and in this post I provide with a simple, general purpose example. I’ll have more to share about INFORMATION_SCHEMA<\/strong> specific solutions shortly.<\/p>\n

I was looking for a server side solution which would not require query changes, apart from directing the query to other tables. Solution has to be supported by all standard MySQL installs; so: no plugins, no special rebuilds.<\/p>\n

Sample data<\/h4>\n

I’ll explain by walking through the solution. Let’s begin with some sample table:<\/p>\n

\n
CREATE TABLE sample_data (\r\n\u00a0 id INT UNSIGNED NOT NULL PRIMARY KEY,\r\n\u00a0 dt DATETIME,\r\n\u00a0 msg VARCHAR(128) CHARSET ascii\r\n);\r\n\r\nINSERT INTO sample_data VALUES (1, NOW(), 'sample txt');\r\nINSERT INTO sample_data VALUES (2, NOW(), 'sample txt');\r\nINSERT INTO sample_data VALUES (3, NOW(), 'sample txt');\r\n\r\nSELECT * FROM sample_data;\r\n+----+---------------------+------------+\r\n| id | dt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | msg\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+---------------------+------------+\r\n|\u00a0 1 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 2 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 3 | 2011-11-24 11:01:30 | sample txt |\r\n+----+---------------------+------------+<\/pre>\n<\/blockquote>\n
In this simplistic example, I wish to create a construct which looks exactly like sample_data<\/strong>, but which caches data according to some heuristic. It will, in fact, cache the entire content of sample_data<\/strong>.<\/p>\n
That much is not a problem: just create another table to cache the data:<\/p>\n
\n
CREATE TABLE cache_sample_data LIKE sample_data;<\/pre>\n<\/blockquote>\n
The big question is: how do you make the table invalidate itself while SELECT<\/strong>ing from it?<\/p>\n
Here’s the deal. I’ll ask for your patience while I draw the outline, and start with failed solutions. By the end, everything will work.<\/p>\n
Failed attempt: purge rows from the table even while reading it<\/h4>\n
My idea is to create a stored function which purges the cache_sample_data<\/strong> table, then fills in with fresh data, according to some heuristic. Something like this:<\/p>\n
\n
DELIMITER $$\r\n\r\nCREATE FUNCTION `revalidate_cache_sample_data`() RETURNS tinyint unsigned\r\n\u00a0\u00a0\u00a0 MODIFIES SQL DATA\r\n\u00a0\u00a0\u00a0 DETERMINISTIC\r\n\u00a0\u00a0\u00a0 SQL SECURITY INVOKER\r\nBEGIN\r\n\u00a0 if(rand() > 0.1) then\r\n\u00a0\u00a0\u00a0 return 0; -- simplistic heuristic\r\n\u00a0 end if;\r\n\r\n\u00a0 DELETE FROM cache_sample_data;\r\n\u00a0 INSERT INTO cache_sample_data SELECT * FROM sample_data;\r\n\u00a0 RETURN 0;\r\nEND $$\r\n\r\nDELIMITER ;<\/pre>\n<\/blockquote>\n
So the function uses some heuristic. It’s a funny RAND()<\/strong> in our case; you will want to check up on time stamps, or some flags, what have you. But this is not the important part here, and I want to keep the focus on the main logic.<\/p>\n
Upon deciding the table needs refreshing, the function purges all rows, then copies everything from sample_data<\/strong>. Sounds fair enough?<\/p>\n
Let’s try and invoke it. Just write some query by hand:<\/p>\n
\n
mysql> SELECT revalidate_cache_sample_data();\r\n+--------------------------------+\r\n| revalidate_cache_sample_data() |\r\n+--------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/strong> |\r\n+--------------------------------+\r\n\r\nmysql> SELECT revalidate_cache_sample_data();\r\n+--------------------------------+\r\n| revalidate_cache_sample_data() |\r\n+--------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0<\/strong> |\r\n+--------------------------------+\r\n\r\nmysql> SELECT revalidate_cache_sample_data();\r\n+--------------------------------+\r\n| revalidate_cache_sample_data() |\r\n+--------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1<\/strong> |\r\n+--------------------------------+<\/pre>\n<\/blockquote>\n
First two invocations – nothing. The third one indicated a revalidation of cache data. Let’s verify:<\/p>\n
\n
mysql> SELECT * FROM cache_sample_data;\r\n+----+---------------------+------------+\r\n| id | dt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | msg\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+---------------------+------------+\r\n|\u00a0 1 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 2 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 3 | 2011-11-24 11:01:30 | sample txt |\r\n+----+---------------------+------------+<\/pre>\n<\/blockquote>\n
OK, seems like the function works.<\/p>\n
We now gather some courage, and try combining calling to this function even while SELECTing from the cache table, like this:<\/p>\n
\n
SELECT\r\n\u00a0 cache_sample_data.*\r\nFROM\r\n\u00a0 cache_sample_data,\r\n\u00a0 (SELECT revalidate_cache_sample_data()) AS select_revalidate\r\n;\r\n+----+---------------------+------------+\r\n| id | dt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | msg\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+---------------------+------------+\r\n|\u00a0 1 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 2 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 3 | 2011-11-24 11:01:30 | sample txt |\r\n+----+---------------------+------------+<\/pre>\n<\/blockquote>\n
To explain what happens in the above query, consider its programmatic nature<\/a>: we create a derived table, populated by the function’s result. That means the function is invoked in order to generate the derived table. The derived table itself must be materialized before the query begins execution, and so it is that we first invoke the function, then make the SELECT<\/strong>.<\/p>\n
Don’t open the champagne yet. While the above paragraph is correct, we are deceived: in this last invocation, the function did not<\/strong> attempt a revalidation. The RAND()<\/strong> function just didn’t provide with the right value.<\/p>\n
Let’s try again:<\/p>\n
\n
SELECT\r\n\u00a0 cache_sample_data.*\r\nFROM\r\n\u00a0 cache_sample_data,\r\n\u00a0 (SELECT revalidate_cache_sample_data()) AS select_revalidate\r\n;\r\nERROR 1442 (HY000): Can't update table 'cache_sample_data' in stored function\/trigger because it is already used by statement which invoked this stored function\/trigger.<\/strong><\/pre>\n<\/blockquote>\n
Aha! Bad news. The MySQL manual says on Restrictions on Stored Programs<\/a>:<\/p>\n
A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.<\/p><\/blockquote>\n
Anyone to the rescue?<\/h4>\n
I was quite upset. Can we not make this work? At sorrow times like these, one reflects back on words of wiser people. What would Roland Bouman<\/a> say on this?<\/p>\n
Oh, yes; he would say: “we can use a FEDERATED<\/strong> table which connect onto itself, thus bypass the above restriction”<\/em>.<\/p>\n
Unfortunately, FEDERATED<\/strong> is by default disabled nowadays; I cannot rely on its existence. Besides, to use FEDERATED<\/strong> one has to fill in passwords and stuff. Definitely not an out-of-the-box solution in this case.<\/p>\n
Few more days gone by. Decided the problem cannot be solved. And then it hit me.<\/p>\n
MyISAM to the rescue<\/h4>\n
MyISAM<\/strong><\/em>? Really?<\/p>\n
MERGE<\/strong> reflects the data contained within MyISAM<\/strong> tables. Perhaps the most common use for MERGE<\/strong> is to work out partitioned-like table of records, with MyISAM<\/strong> table-per month, and an overlooking MERGE<\/strong> table dynamically adding and removing tables from its view.<\/p>\n
But I intend for MERGE<\/strong> a different use: just be an identical reflection of cache_sample_data<\/strong>.<\/p>\n
So we must work out the following:<\/p>\n
\n
ALTER TABLE cache_sample_data<\/strong> ENGINE=MyISAM<\/strong>;\r\nCREATE TABLE cache_sample_data_wrapper<\/strong> LIKE cache_sample_data;\r\nALTER TABLE cache_sample_data_wrapper<\/strong> ENGINE=MERGE<\/strong> UNION=(cache_sample_data)<\/strong>;<\/pre>\n<\/blockquote>\n
I just want to verify the new table is setup correctly:<\/p>\n
\n
mysql> SELECT * FROM cache_sample_data_wrapper;\r\n+----+---------------------+------------+\r\n| id | dt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | msg\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+---------------------+------------+\r\n|\u00a0 1 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 2 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 3 | 2011-11-24 11:01:30 | sample txt |\r\n+----+---------------------+------------+<\/pre>\n<\/blockquote>\n
Seems fine.<\/p>\n
So the next step is what makes the difference: the two tables are not the same<\/em>. One relies on the other<\/em>, but they are distinct. Our function DELETE<\/strong>s from and INSERT<\/strong>s to cached_sample_data<\/strong>, but it does not affect, nor lock<\/em>, cache_sample_data_wrapper<\/strong>.<\/p>\n
We now rewrite our query to read:<\/p>\n
\n
SELECT\r\n\u00a0 cache_sample_data_wrapper.*\r\nFROM\r\n\u00a0 cache_sample_data_wrapper<\/strong>,\r\n\u00a0 (SELECT revalidate_cache_sample_data()) AS select_revalidate\r\n;<\/pre>\n<\/blockquote>\n
This query is perfectly valid. It works. To illustrate, I do:<\/p>\n
\n
-- Try this a few times till RAND() is lucky:\r\n\r\nTRUNCATE<\/strong> cache_sample_data;\r\n\r\nSELECT\r\n\u00a0 cache_sample_data_wrapper.*\r\nFROM\r\n\u00a0 cache_sample_data_wrapper,\r\n\u00a0 (SELECT revalidate_cache_sample_data()) AS select_revalidate\r\n;\r\n+----+---------------------+------------+\r\n| id | dt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | msg\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+---------------------+------------+\r\n|\u00a0 1 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 2 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 3 | 2011-11-24 11:01:30 | sample txt |\r\n+----+---------------------+------------+<\/pre>\n<\/blockquote>\n
Whoa! Where did all this data come from? Didn’t we just TRUNCATE<\/strong> the table?<\/p>\n
The query worked. The function re-populated cache_sample_data<\/strong>.<\/p>\n
The final touch<\/h4>\n
Isn’t the above query just beautiful<\/em>? I suppose not many will share my opinion. What happened to my declaration that “the original query need not be changed, apart from querying a different table”<\/em>?<\/p>\n
Yes, indeed. It’s now time for the final touch. There’s nothing amazing in this step, but we all know the way it is packaged is what makes the sale. We will now use views<\/em>. We use two of them since a view must not contain a subquery<\/em> in the FROM<\/strong> clause. Here goes:<\/p>\n
\n
CREATE OR REPLACE VIEW revalidate_cache_sample_data_view<\/strong> AS\r\n\u00a0 SELECT revalidate_cache_sample_data()\r\n;\r\n\r\nCREATE OR REPLACE VIEW autocache_sample_data<\/strong> AS\r\n\u00a0 SELECT\r\n\u00a0\u00a0\u00a0 cache_sample_data_wrapper.*\r\n\u00a0 FROM\r\n\u00a0\u00a0\u00a0 cache_sample_data_wrapper,\r\n\u00a0\u00a0\u00a0 revalidate_cache_sample_data_view\r\n;<\/pre>\n<\/blockquote>\n
And finally, we can make a very simple query like this:<\/p>\n
\n
SELECT * FROM autocache_sample_data<\/strong>;\r\n--\r\n-- Magic in work now!<\/span><\/strong>\r\n--\r\n+----+---------------------+------------+\r\n| id | dt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | msg\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+---------------------+------------+\r\n|\u00a0 1 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 2 | 2011-11-24 11:01:30 | sample txt |\r\n|\u00a0 3 | 2011-11-24 11:01:30 | sample txt |\r\n+----+---------------------+------------+<\/pre>\n<\/blockquote>\n
Much as we would query the original sample_data<\/strong> table.<\/p>\n
Summary<\/h4>\n
So what have we got? A stored routine, a MyISAM<\/strong> table, a MERGE<\/strong> table and two views. Quite a lot of constructs just to cache a table! But a beautiful cache access: plain old SQL queries<\/em>. The flow looks like this:<\/p>\n
\"\"<\/a><\/p><\/blockquote>\n
Our cache table is a MyISAM<\/strong> table. It can get corrupted, which is bad. But not completely bad: it’s nothing more than a cache; we can throw away its entire data, and revalidate. We can actually ask the function to revalidate (say, pass a parameter).<\/p>\n","protected":false},"excerpt":{"rendered":"
Is there a way to create a caching table, some sort of a materialized view, such that upon selecting from that table, its data is validated\/invalidated? Hint: yes. But to elaborate the point: say I have some table data_table. Can I rewrite all my queries which access data_table to read from some autocache_data_table, but have […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[74,13,21,59,66],"class_list":["post-4353","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-hack","tag-myisam","tag-sql","tag-stored-routines","tag-views"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-18d","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4353","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=4353"}],"version-history":[{"count":39,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4353\/revisions"}],"predecessor-version":[{"id":4756,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/4353\/revisions\/4756"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=4353"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=4353"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=4353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
Yes, and not only MyISAM<\/strong>, but also its cousin: it’s long abandoned cousin, forgotten once views<\/strong> and partitions<\/strong> came into MySQL. MERGE<\/a><\/strong>.<\/p>\n