{"id":3015,"date":"2014-09-10T03:38:44","date_gmt":"2014-09-10T03:38:44","guid":{"rendered":"http:\/\/www.deuzebranaweb.com.br\/?p=3015"},"modified":"2014-09-10T03:38:44","modified_gmt":"2014-09-10T03:38:44","slug":"convert-myisam-tables-to-innodb","status":"publish","type":"post","link":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/2014\/09\/10\/convert-myisam-tables-to-innodb\/","title":{"rendered":"Convert MyISAM tables to InnoDB"},"content":{"rendered":"
\n

Convert MyISAM tables to InnoDB<\/h1>\n

\n

If you want to convert a MyISAM table to InnoDB, the process is fairly easy, but you can do something extra to speed things up. Before converting the table, adjust its order so that the primary key column is in order:<\/p>\n

\n\n\n\n
\n
ALTER TABLE tablename ORDER BY 'primary_key_column';<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
This will pre-arrange the table so that it can be converted quickly without a lot of re-arranging required in MySQL. Then, simply change the table engine:<\/p>\n
\n\n\n\n
\n
ALTER TABLE tablename ENGINE = INNODB;<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
If your table is large, then it may take a while to convert it over. There will probably be a fair amount of CPU usage and disk I\/O in the process.<\/p>\n
These statements are also safe in replicated environments. When you issue this statement to the master, it will begin the conversion process. Once it is complete on the master, the statement will roll down to the slaves, and they will begin the conversion as well. Keep in mind, however, that this can greatly reduce the performance of your configuration in the process.<\/p>\n
http:\/\/major.io\/2007\/10\/03\/convert-myisam-tables-to-innodb\/<\/p>\n
http:\/\/www.oficinadanet.com.br\/artigo\/mysql\/mysql-como-converter-innodb-para-myisam<\/p>\n
 <\/p>\n
 <\/p>\n
Para alterar a tabela teste para InnoDB execute o seguinte comando:<\/strong><\/p>\n
\nALTER\u00a0TABLE\u00a0`teste`\u00a0ENGINE\u00a0=\u00a0InnoDB\n<\/code><\/pre>\n
Para fazer o contr\u00e1rio, transformar InnoDB para MyISAM execute o seguinte comando:<\/p>\n
\nALTER\u00a0TABLE\u00a0`teste`\u00a0ENGINE\u00a0=\u00a0MYISAM\n<\/code><\/pre>\n
Como alterar ent\u00e3o os tipos usando o phpMyAdmin:<\/h2>\n
No phpMyAdmin, crie uma tabela chamada teste, com os campos id INT(11) e titulo VARCHAR(200), conforme figura abaixo:<\/p>\n
\n
\"http:\/\/www.oficinadanet.com.br\/\/imagens\/coluna\/3369\/\/img1.png\"<\/div>\n<\/blockquote>\n
Defina o nome da tabela teste e clique em executar. Ap\u00f3s isto aparecer\u00e1 uma tela pedindo para colocar os campos, conforme tela abaixo:<\/p>\n
\n
\"http:\/\/www.oficinadanet.com.br\/\/imagens\/coluna\/3369\/\/img2.png\"<\/div>\n<\/blockquote>\n
Defina o nome dos campos id INT(11) e fa\u00e7a ele como chave prim\u00e1ria, e outro campo chamado titulo VARCHAR(200). Voc\u00ea pode definir o tipo da tabela, no caso escolhi MyISAM. E depois clique em executar.<\/p>\n
Feito isto, acesse a tabela teste<\/strong>, na barra lateral esquerda, e ent\u00e3o clique na aba opera\u00e7\u00f5es<\/strong>, vai ter uma caixa chamada Op\u00e7\u00f5es da tabela<\/strong>, tem um campo chamado Storage Engine<\/strong>, basta escolher o tipo que quiser para alterar e apertar o bot\u00e3o executar, conforme a figura abaixo:<\/p>\n
\n
\"http:\/\/www.oficinadanet.com.br\/\/imagens\/coluna\/3369\/\/img3.png\"<\/div>\n<\/blockquote>\n
 <\/p>\n
Aten\u00e7\u00e3o:<\/strong> Certifique-se de que voc\u00ea n\u00e3o ir\u00e1 ocupar todo o tablespace: tabelas InnoDB gasta muito mais espa\u00e7o que tabelas MyISAM. Se um ALTER TABLE ficar sem espa\u00e7o, ele ir\u00e1 iniciar um rollback, que pode levar horas se ele estiver no limite de disco. Para inser\u00e7\u00f5es, o InnoDB utiliza o buffer de inser\u00e7\u00e3o para fundir registros de \u00edndices secund\u00e1rios a \u00edndices em grupos. Isto economiza muito a E\/S de disco.<\/p><\/blockquote>\n
Qualquer d\u00favida pergunte no formul\u00e1rio abaixo \ud83d\ude09<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Convert MyISAM tables to InnoDB October 3, 2007 By Major Hayden 14 Comments If you want to convert a MyISAM table to InnoDB, the process is fairly easy, but you can do something extra to speed things up. Before converting the table, adjust its order…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_angie_page":false,"page_builder":"","footnotes":""},"categories":[13,7],"tags":[],"class_list":["post-3015","post","type-post","status-publish","format-standard","hentry","category-msql","category-wordpress"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3015","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=3015"}],"version-history":[{"count":0,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3015\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3015"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3015"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.deuzebranaweb.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3015"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}