{"id":1704,"date":"2020-10-28T10:23:41","date_gmt":"2020-10-28T09:23:41","guid":{"rendered":"https:\/\/blog.mhasin.eu\/?p=1704"},"modified":"2020-10-31T21:20:04","modified_gmt":"2020-10-31T20:20:04","slug":"vala-pdns-mysql-table-optimize","status":"publish","type":"post","link":"https:\/\/blog.mhasin.eu\/?p=1704","title":{"rendered":"VALA PDNS mysql table optimize"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Povodny select na tabulku record:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`147.232.191.6` SQL SECURITY DEFINER VIEW `records` AS select `lmsfin`.`dns_pdns`.`id` + 50 AS `id`,cast(`lmsfin`.`dns_pdns`.`domain_id` as signed) AS `domain_id`,cast(`lmsfin`.`dns_pdns`.`name` as char(255) charset utf8mb4) AS `name`,cast(`lmsfin`.`dns_pdns`.`type` as char(10) charset utf8mb4) AS `type`,cast(`lmsfin`.`dns_pdns`.`content` as char(64000) charset utf8mb4) AS `content`,`lmsfin`.`dns_pdns`.`ttl` AS `ttl`,`lmsfin`.`dns_pdns`.`prio` AS `prio`,`lmsfin`.`dns_pdns`.`change_date` AS `change_date`,`lmsfin`.`dns_pdns`.`disabled` AS `disabled`,cast(`lmsfin`.`dns_pdns`.`ordername` as char(255) charset utf8mb4) AS `ordername`,`lmsfin`.`dns_pdns`.`auth` AS `auth` from `lmsfin`.`dns_pdns` union select `lmsfin`.`nodes`.`id` + 10000 AS `id`,cast((select `lmsfin`.`servis_zona`.`dnszone` AS `dnszone` from `lmsfin`.`servis_zona` where `lmsfin`.`servis_zona`.`seq` = (select `lmsfin`.`customers`.`seq_servis_zona` AS `seq_servis_zona` from `lmsfin`.`customers` where `lmsfin`.`customers`.`id` = `lmsfin`.`nodes`.`ownerid`)) as signed) AS `domain_id`,concat_ws('.',lcase(`lmsfin`.`nodes`.`name`),(select `lmsfin`.`domains`.`name` from `lmsfin`.`domains` where `lmsfin`.`domains`.`id` = (select `lmsfin`.`servis_zona`.`dnszone` AS `dnszone` from `lmsfin`.`servis_zona` where `lmsfin`.`servis_zona`.`seq` = (select `lmsfin`.`customers`.`seq_servis_zona` AS `seq_servis_zona` from `lmsfin`.`customers` where `lmsfin`.`customers`.`id` = `lmsfin`.`nodes`.`ownerid`)))) AS `name`,'A' AS `type`,cast(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`) as char(64000) charset utf8mb4) AS `content`,86400 AS `ttl`,NULL AS `prio`,0 AS `change_date`,0 AS `disabled`,cast(NULL as char(255) charset utf8mb4) AS `ordername`,1 AS `auth` from `lmsfin`.`nodes` where `lmsfin`.`nodes`.`access` = 1 and (select `lmsfin`.`customers`.`status` from `lmsfin`.`customers` where `lmsfin`.`customers`.`id` = `lmsfin`.`nodes`.`ownerid`) <> 1 union select cast(`lmsfin`.`nodes`.`id` as signed) + 1000000 AS `id`,cast(`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',3) as signed) AS `domain_id`,cast(concat_ws('.',`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',4),`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',3),`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',2),`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',1),'in-addr.arpa') as char(255) charset utf8mb4) AS `name`,'PTR' AS `type`,cast(concat(lcase(`lmsfin`.`nodes`.`name`),concat(_utf8'.',(select `lmsfin`.`soa`.`origin` AS `origin` from `lmsfin`.`soa` where `lmsfin`.`soa`.`id` = (select `lmsfin`.`servis_zona`.`dnszone` AS `dnszone` from `lmsfin`.`servis_zona` where `lmsfin`.`servis_zona`.`seq` = (select `lmsfin`.`customers`.`seq_servis_zona` AS `seq_servis_zona` from `lmsfin`.`customers` where `lmsfin`.`customers`.`id` = `lmsfin`.`nodes`.`ownerid`))))) as char(64000) charset utf8mb4) AS `content`,86400 AS `ttl`,NULL AS `prio`,0 AS `change_date`,0 AS `disabled`,cast(NULL as char(255) charset utf8mb4) AS `ordername`,1 AS `auth` from `lmsfin`.`nodes` where `lmsfin`.`nodes`.`access` = 1 and `lmsfin`.`nodes`.`linktype` = 0 and (select `lmsfin`.`customers`.`status` from `lmsfin`.`customers` where `lmsfin`.`customers`.`id` = `lmsfin`.`nodes`.`ownerid`) <> 1 union select `lmsfin`.`soa`.`id` AS `id`,`lmsfin`.`soa`.`id` AS `domain_id`,`lmsfin`.`soa`.`origin` AS `name`,'SOA' AS `type`,concat_ws(' ',`lmsfin`.`soa`.`ns`,`lmsfin`.`soa`.`mbox`,`lmsfin`.`soa`.`serial`,`lmsfin`.`soa`.`refresh`,`lmsfin`.`soa`.`retry`,`lmsfin`.`soa`.`expire`,`lmsfin`.`soa`.`minimum`) AS `content`,`lmsfin`.`soa`.`ttl` AS `ttl`,NULL AS `prio`,0 AS `change_date`,0 AS `disabled`,'' AS `ordername`,1 AS `auth` from `lmsfin`.`soa`<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Nova verzia:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select `lmsfin`.`dns_pdns`.`id` + 50 AS `id`,\n\n\n\ncast(`lmsfin`.`dns_pdns`.`domain_id` as signed) AS `domain_id`,\ncast(`lmsfin`.`dns_pdns`.`name` as char(255) charset utf8mb4) AS `name`,\ncast(`lmsfin`.`dns_pdns`.`type` as char(10) charset utf8mb4) AS `type`,\ncast(`lmsfin`.`dns_pdns`.`content` as char(64000) charset utf8mb4) AS `content`,\n`lmsfin`.`dns_pdns`.`ttl` AS `ttl`,\n`lmsfin`.`dns_pdns`.`prio` AS `prio`,\n`lmsfin`.`dns_pdns`.`change_date` AS `change_date`,\n`lmsfin`.`dns_pdns`.`disabled` AS `disabled`,\ncast(`lmsfin`.`dns_pdns`.`ordername` as char(255) charset utf8mb4) AS `ordername`,\n`lmsfin`.`dns_pdns`.`auth` AS `auth` from `lmsfin`.`dns_pdns` union \n\n\n\nselect `lmsfin`.`nodes`.`id` + 10000 AS `id`,\n `lmsfin`.`servis_zona`.`dnszone` AS    `domain_id`,\n concat_ws('.',lcase(`lmsfin`.`nodes`.`name`),`lmsfin`.domains.name) as `name`,\n  'A' AS `type`,\n   cast(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`) as char(64000) charset utf8mb4) AS `content`\n   ,86400 AS `ttl`\n   ,NULL AS `prio`\n   ,`lmsfin`.nodes.moddate AS `change_date`,\n   0 AS `disabled`,\n   cast(NULL as char(255) charset utf8mb4) AS `ordername`,\n   1 AS `auth` \n    from `lmsfin`.nodes\n\n\ninner join `lmsfin`.customers on `lmsfin`.customers.id = `lmsfin`.nodes.ownerid \ninner join `lmsfin`.servis_zona on `lmsfin`.customers.seq_servis_zona = `lmsfin`.servis_zona.seq\ninner join `lmsfin`.domains on `lmsfin`.servis_zona.dnszone = `lmsfin`.domains.id\ninner join `lmsfin`.passwd on `lmsfin`.passwd.ownerid = `lmsfin`.nodes.ownerid and `lmsfin`.passwd.`expdate` > unix_timestamp()\nwhere `lmsfin`.`nodes`.`access` = 1\n\n\nunion select `lmsfin`.`nodes`.`id` + 1000000 AS `id`,\n\ncast(`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',3) as signed) AS `domain_id`,\n\n\n\ncast(concat_ws('.',`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',4),`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',3),`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',2),`SPLIT_STRING`(inet_ntoa(`lmsfin`.`nodes`.`ipaddr`),'.',1),'in-addr.arpa') as char(255) charset utf8mb4) AS `name`,\n\n'PTR' AS `type`,\n\n\ncast(concat(lcase(`lmsfin`.`nodes`.`name`),concat(_utf8'.',(select `lmsfin`.`soa`.`origin` AS `origin` from `lmsfin`.`soa` where `lmsfin`.`soa`.`id` = `lmsfin`.servis_zona.dnszone ))) as char(64000) charset utf8mb4) AS `content`,\n\n\n86400 AS `ttl`,\n\nNULL AS `prio`,\n`lmsfin`.nodes.moddate AS `change_date`,\n0 AS `disabled`,\n\ncast(NULL as char(255) charset utf8mb4) AS `ordername`,1 AS `auth` from `lmsfin`.`nodes` \n\ninner join `lmsfin`.customers on `lmsfin`.customers.id = `lmsfin`.nodes.ownerid \ninner join `lmsfin`.servis_zona on `lmsfin`.customers.seq_servis_zona = `lmsfin`.servis_zona.seq\ninner join `lmsfin`.domains on `lmsfin`.servis_zona.dnszone = `lmsfin`.domains.id\ninner join `lmsfin`.passwd on `lmsfin`.passwd.ownerid = `lmsfin`.nodes.ownerid and `lmsfin`.passwd.`expdate` > unix_timestamp()\n\n\nwhere `lmsfin`.`nodes`.`access` = 1\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\nunion select `lmsfin`.`soa`.`id` AS `id`,`lmsfin`.`soa`.`id` AS `domain_id`,`lmsfin`.`soa`.`origin` AS `name`,'SOA' AS `type`,concat_ws(' ',`lmsfin`.`soa`.`ns`,`lmsfin`.`soa`.`mbox`,`lmsfin`.`soa`.`serial`,`lmsfin`.`soa`.`refresh`,`lmsfin`.`soa`.`retry`,`lmsfin`.`soa`.`expire`,`lmsfin`.`soa`.`minimum`) AS `content`,`lmsfin`.`soa`.`ttl` AS `ttl`,NULL AS `prio`,0 AS `change_date`,0 AS `disabled`,'' AS `ordername`,1 AS `auth` from `lmsfin`.`soa`<\/code><\/pre>\n<div class=\"pdf24Plugin-cp\"> \t<form name=\"pdf24Form0\" method=\"post\" action=\"https:\/\/doc2pdf.pdf24.org\/wordpress.php\" target=\"pdf24PopWin\" onsubmit=\"var pdf24Win = window.open('about:blank', 'pdf24PopWin', 'resizable=yes,scrollbars=yes,width=600,height=250,left='+(screen.width\/2-300)+',top='+(screen.height\/3-125)+''); pdf24Win.focus(); if(typeof pdf24OnCreatePDF === 'function'){void(pdf24OnCreatePDF(this,pdf24Win));}\"> \t\t<input type=\"hidden\" name=\"blogCharset\" value=\"Cw1x07UAAA==\" \/><input type=\"hidden\" name=\"blogPosts\" value=\"MwQA\" \/><input type=\"hidden\" name=\"blogUrl\" value=\"yygpKSi20tdPyslP18vNSCzOzNNLLQUA\" \/><input type=\"hidden\" name=\"blogName\" value=\"c\/LxdwcA\" \/><input type=\"hidden\" name=\"blogValueEncoding\" value=\"gzdeflate base64\" \/><input type=\"hidden\" name=\"postId_0\" value=\"MzQ3MAEA\" \/><input type=\"hidden\" name=\"postTitle_0\" value=\"C3P0cVQIcPELVsitLC7MUShJTMpJVcgvKMnMzaxKBQA=\" \/><input type=\"hidden\" name=\"postLink_0\" value=\"yygpKSi20tdPyslP18vNSCzOzNNLLdW3L7A1NDcwAQA=\" \/><input type=\"hidden\" name=\"postAuthor_0\" value=\"y00syfcwNAIA\" \/><input type=\"hidden\" name=\"postDateTime_0\" value=\"MzIwMtA1NNA1slAwNLACIhNDAA==\" \/><input type=\"hidden\" name=\"postContent_0\" value=\"7Vhbc+I2FH7Xr9DkoeApYSGbbLctMM3s0i0zLJsCaadPtrCVRI0tuZIIyf76lSyDrxCbkplMix+CfHwuupzzHX0BvRC6PhKif7IKTxc+c+9PQ8TRLUfh3cngij0wjz5BgX3sSkgRlGix9O+XkGOXce+n3ptwANTTCzkuOHKZh08GPf0z+DAdXs6H8HL86ct0NP\/tc\/968nH462gy\/AjN77TvcMak84vTPf+hffb2rN39sdt+58DZ72M4G364VmZ\/rXXhH6Phn9AxkxAOvJytp+j4gbgh1Gk7HhV2qP6oIfEc+D286Gg9\/dJykZDNUlWPBYhQW1sgAQW5pdizIrvkyw5zigIcWbp3iDfPLi6saCSwhEt58z5YnBtnkd4OP\/IpTPnpdra4idR2uHEZlZjKxNO7805nm7O1cqt8RtJ3TEw1KFcJOWFGJxqVK6nQ9BbbHpLY6KYF5SYeEWjhY8\/ob952rFtlBeZVzyJRLg+PlvLOhDajG86CsjyDS0oYLSYiVfmfZGFXHUAuEZsFE4H5AxH2V0aRiaBG8XZtXnLTSJvA1R1W9bjNn8D\/OLAPi2HdpZAswFwYJTvjUwcvCHOTSBwUppD2rXeiX7JDbEUxVx8ta1fxMeoiaa9Es9FutHy1g7hZdBWdp9UqrtF4Smo1f5rx58L8Ezsz++OhFQ7NSmNb47KRhyhCsbSpZKjkvEiIPI87Vj2keq+1ElSaXI\/HKfzpFPGlUwYhkVktnOhuhwOzoMJOrteJXBcLoXeyCxH1njtPieRSvNiJwe98+fNAzSSDXDlYTcNXqipjJMtjmTO7Go\/m9mw+HU0+OZWOvKXr+K31XL\/N1v2+cc6t1v5T3Nv0bH\/Trvoh9FRL2oiHqGFVvl40rubTQhGabWw+g5uxlq296lmUoB1DUaMlt+o1rg8zzmMcK8O2yPqIpFuR9H8HhEUNn9B7cw\/uw85rBcuylF4D4rZvKWTbVU9xEc++ZDtpAoOwUbBXF5SCLFiwx6JU5TFBflHO8Q3H4q7sg+RPRTF+DAnHJVEJJcEycLZyC6OWpRX187bRqJGSOmTvTcRHFXHlOKauuxjwhD0g+ID5V4JSZHeXRV2tZ4hzLV6rPf5LbgsORW7BgdgtODS9BRX4LahEcMEeDBfUorjg8BwXHILk6kSrRXRBpfaunkwuwlpkb\/Mh5mptLY8a6TolIczSEiWAL0NNtOdcV45EWYSLRJuIUcB2wDydKSWZo5Tz8LdZQP3WrU3TWKnfc8ceTUidNKGqB8K\/GaHJt03vhKxM2iZeup2apcW9FJY6TGXFFpe561faf0qs1UoDxFmRcZ62ixMx7XadSMQr9Rgq2F55GYdGtFnp1i3IXHliI91LDWoMdJk92pIEWN1qgrBpgQr3KHVS1f8Fla5N8HLUbdORjvStLn0DIE\/gsnv5mjjczoKqzWbUOnOEBoDc1RBURs0CZIIX4zpHpHwNSAkqYmXuOdK6\/w6t+wY=\" \/> \t\t<a href=\"https:\/\/www.pdf24.org\" target=\"_blank\" title=\"www.pdf24.org\" rel=\"nofollow\"><img src=\"https:\/\/blog.mhasin.eu\/wp-content\/plugins\/pdf24-post-to-pdf\/img\/pdf_32x32.png\" alt=\"\" border=\"0\" height=\"32\" \/><\/a> \t\t<span class=\"pdf24Plugin-cp-space\">\u00a0\u00a0<\/span> \t\t<span class=\"pdf24Plugin-cp-text\">Send article as PDF<\/span> \t\t<span class=\"pdf24Plugin-cp-space\">\u00a0\u00a0<\/span> \t\t<input class=\"pdf24Plugin-cp-input\" style=\"margin: 0px;\" type=\"text\" name=\"sendEmailTo\" placeholder=\"Enter email address\" \/> \t\t<input class=\"pdf24Plugin-cp-submit\" style=\"margin: 0px;\" type=\"submit\" value=\"Send\" \/> \t<\/form> <\/div>","protected":false},"excerpt":{"rendered":"Povodny select na tabulku record: Nova verzia: \u00a0\u00a0 Send article as PDF \u00a0\u00a0\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"arc_restricted_post":false,"footnotes":""},"categories":[7],"tags":[],"class_list":["post-1704","post","type-post","status-publish","format-standard","hentry","category-programovanie"],"_links":{"self":[{"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=\/wp\/v2\/posts\/1704","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1704"}],"version-history":[{"count":2,"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=\/wp\/v2\/posts\/1704\/revisions"}],"predecessor-version":[{"id":1720,"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=\/wp\/v2\/posts\/1704\/revisions\/1720"}],"wp:attachment":[{"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1704"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1704"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.mhasin.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1704"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}