{"id":641,"date":"2014-02-15T16:25:14","date_gmt":"2014-02-15T16:25:14","guid":{"rendered":"http:\/\/elbsolutions.com\/projects\/?p=641"},"modified":"2022-02-03T11:25:03","modified_gmt":"2022-02-03T17:25:03","slug":"mysql-tables-growing-7-million-rows-optimized-another-way","status":"publish","type":"post","link":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/","title":{"rendered":"mySQL Tables growing to 7 million rows cannot be optimized &#8211; here is another way"},"content":{"rendered":"<p>Optimizing these large tables DOES NOT WORK. It corrupts them and you have to talk to tech support to get backups restored. Even when you ssh into the server and do it &#8220;command line.&#8221; I reminded myself of this yesterday when I optimized a table forgetting the better way.<\/p>\n<p>First, back up the sluggish table. It had lots of overhead &#8211; this is a hint. Another way to back it up, is to copy it to a new table. Here&#8217;s how (don&#8217;t do this from phpmyadmin because it will for sure time out). I use a mac so I have sequel_pro AND SET THE TIMEOUT \u00a0TO 1000 seconds or more in the preferences. If you have access to ssh into the machine &#8211; even better.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">&lt;\/pre&gt;\r\nCREATE TABLE xxx_backupddd_nnn LIKE xxx;\r\ninsert into xxx_backupddd_nnn SELECT * FROM xxx;\r\n\r\nRENAME TABLE xxx TO xxx_overheadddd_nnn;\r\nCREATE TABLE xxx LIKE xxx_overheadddd_nnn;\r\nINSERT into xxx SELECT * FROM xxx_backupddd_nnn;\r\n&lt;pre&gt;\r\n\r\n<\/pre>\n<p>In my case xxx is the table and ddd_nnn the date and attempt such as 20140303_1. \u00a0I run each command line by line and carefully look at the output. Sometimes the server times out even after 1\/2 seconds so be watchful.<\/p>\n<p>No tech support phone call or $15 backup recovery fee required. Really &#8211; that is not bad for a recovery fee.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Optimizing these large tables DOES NOT WORK. It corrupts them and you have to talk to tech support to get backups restored. Even when you ssh into the server and do it &#8220;command line.&#8221; I reminded myself of this yesterday when I optimized a table forgetting the better way. First, back up the sluggish table. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-641","post","type-post","status-publish","format-standard","hentry","category-general"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.7 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>mySQL Tables growing to 7 million rows cannot be optimized - here is another way - ELB Solutions.com Inc.<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"mySQL Tables growing to 7 million rows cannot be optimized - here is another way - ELB Solutions.com Inc.\" \/>\n<meta property=\"og:description\" content=\"Optimizing these large tables DOES NOT WORK. It corrupts them and you have to talk to tech support to get backups restored. Even when you ssh into the server and do it &#8220;command line.&#8221; I reminded myself of this yesterday when I optimized a table forgetting the better way. First, back up the sluggish table. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/\" \/>\n<meta property=\"og:site_name\" content=\"ELB Solutions.com Inc.\" \/>\n<meta property=\"article:published_time\" content=\"2014-02-15T16:25:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-03T17:25:03+00:00\" \/>\n<meta name=\"author\" content=\"Etienne Bley\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Etienne Bley\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/mysql-tables-growing-7-million-rows-optimized-another-way\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/mysql-tables-growing-7-million-rows-optimized-another-way\\\/\"},\"author\":{\"name\":\"Etienne Bley\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"headline\":\"mySQL Tables growing to 7 million rows cannot be optimized &#8211; here is another way\",\"datePublished\":\"2014-02-15T16:25:14+00:00\",\"dateModified\":\"2022-02-03T17:25:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/mysql-tables-growing-7-million-rows-optimized-another-way\\\/\"},\"wordCount\":246,\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/mysql-tables-growing-7-million-rows-optimized-another-way\\\/\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/mysql-tables-growing-7-million-rows-optimized-another-way\\\/\",\"name\":\"mySQL Tables growing to 7 million rows cannot be optimized - here is another way - ELB Solutions.com Inc.\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\"},\"datePublished\":\"2014-02-15T16:25:14+00:00\",\"dateModified\":\"2022-02-03T17:25:03+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/mysql-tables-growing-7-million-rows-optimized-another-way\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/mysql-tables-growing-7-million-rows-optimized-another-way\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/mysql-tables-growing-7-million-rows-optimized-another-way\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"mySQL Tables growing to 7 million rows cannot be optimized &#8211; here is another way\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\",\"name\":\"ELB Solutions.com Inc.\",\"description\":\"Bringing all your IT Pieces together\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\",\"name\":\"Etienne Bley\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"caption\":\"Etienne Bley\"},\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/author\\\/etienne-bley\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"mySQL Tables growing to 7 million rows cannot be optimized - here is another way - ELB Solutions.com Inc.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/","og_locale":"en_US","og_type":"article","og_title":"mySQL Tables growing to 7 million rows cannot be optimized - here is another way - ELB Solutions.com Inc.","og_description":"Optimizing these large tables DOES NOT WORK. It corrupts them and you have to talk to tech support to get backups restored. Even when you ssh into the server and do it &#8220;command line.&#8221; I reminded myself of this yesterday when I optimized a table forgetting the better way. First, back up the sluggish table. [&hellip;]","og_url":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/","og_site_name":"ELB Solutions.com Inc.","article_published_time":"2014-02-15T16:25:14+00:00","article_modified_time":"2022-02-03T17:25:03+00:00","author":"Etienne Bley","twitter_misc":{"Written by":"Etienne Bley","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/#article","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/"},"author":{"name":"Etienne Bley","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"headline":"mySQL Tables growing to 7 million rows cannot be optimized &#8211; here is another way","datePublished":"2014-02-15T16:25:14+00:00","dateModified":"2022-02-03T17:25:03+00:00","mainEntityOfPage":{"@id":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/"},"wordCount":246,"articleSection":["General"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/","url":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/","name":"mySQL Tables growing to 7 million rows cannot be optimized - here is another way - ELB Solutions.com Inc.","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/#website"},"datePublished":"2014-02-15T16:25:14+00:00","dateModified":"2022-02-03T17:25:03+00:00","author":{"@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"breadcrumb":{"@id":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/elbsolutions.com\/projects\/mysql-tables-growing-7-million-rows-optimized-another-way\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/elbsolutions.com\/projects\/"},{"@type":"ListItem","position":2,"name":"mySQL Tables growing to 7 million rows cannot be optimized &#8211; here is another way"}]},{"@type":"WebSite","@id":"https:\/\/elbsolutions.com\/projects\/#website","url":"https:\/\/elbsolutions.com\/projects\/","name":"ELB Solutions.com Inc.","description":"Bringing all your IT Pieces together","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/elbsolutions.com\/projects\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39","name":"Etienne Bley","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","caption":"Etienne Bley"},"url":"https:\/\/elbsolutions.com\/projects\/author\/etienne-bley\/"}]}},"_links":{"self":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/641","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/comments?post=641"}],"version-history":[{"count":6,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/641\/revisions"}],"predecessor-version":[{"id":697,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/641\/revisions\/697"}],"wp:attachment":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/media?parent=641"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/categories?post=641"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/tags?post=641"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}