My goal was to create a table which is partitioned and partially archived (compressed and read-only mode).
\nI tried to compress only one partition and change the permissions. No success.<\/p>\n
It was partially achieved by using combination with engine called merge<\/strong>. <\/p>\n Now we create read-only compressed table (test_arch);<\/p>\n Next, flush table through SQL:<\/p>\n Now, your table is in read-only mode as well as compressed.<\/p>\n However, it is not safe for insert DML which has explicit literal for unique key, e.g.: I am going to use it on production in some more complex way. <\/p>\n Additional advantages<\/strong> (besides these obvious) that I see is: My goal was to create a table which is partitioned and partially archived (compressed and read-only mode). I tried to compress only one partition and change the permissions. No success. It was partially achieved by using combination with engine called merge. CREATE TABLE IF NOT EXISTS `test_arch` ( `id` int(11) NOT NULL AUTO_INCREMENT, `txt` TEXT […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7,3],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/450"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=450"}],"version-history":[{"count":28,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/450\/revisions"}],"predecessor-version":[{"id":479,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/450\/revisions\/479"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=450"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=450"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=450"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\nCREATE TABLE IF NOT EXISTS `test_arch` (\r\n `id` int(11) NOT NULL AUTO_INCREMENT,\r\n `txt` TEXT ,\r\n UNIQUE KEY `id` (`id`)\r\n) ENGINE=MyISAM ;\r\n\r\nCREATE TABLE IF NOT EXISTS `test_current` (\r\n `id` int(11) NOT NULL AUTO_INCREMENT,\r\n `txt` TEXT ,\r\n UNIQUE KEY `id` (`id`)\r\n) ENGINE=MyISAM \/* some partitions *\/ ;\r\n\r\nCREATE TABLE IF NOT EXISTS `test` (\r\n `id` int(11) NOT NULL AUTO_INCREMENT,\r\n `txt` TEXT ,\r\n UNIQUE KEY `id_idx` (`id`)\r\n) ENGINE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(`test_current`,`test_arch`)\r\n\r\n-- you have to fulfill table with some data in order to use .\/myisampack\r\n\r\ninsert into test_arch (`id`, `txt`) VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, ''), VALUES(NULL, '');\r\nupdate test set `txt`='Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam';\r\n<\/pre>\n
\r\n.\/myisampack \/db_storage\/data\/test\/test_arch\r\n.\/myisamchk -rq \/db_storage\/data\/test\/test_arch\r\n<\/pre>\n
\r\nFLUSH TABLE test_arch;\r\n<\/pre>\n
\n“INSERT INTO … (`id`) VALUES(123) … ON DUPLICATE KEY UPDATE … “;
\nor
\n“INSERT INTO … (`id`) VALUES(123)”
\nOnly table indicated in option INSERT_METHOD=FIRST is used to check whether the record exists or not.<\/p>\n
\n* permission limitation (you can make delete w\/o success. You can even drop the table but data);<\/p>\n","protected":false},"excerpt":{"rendered":"